public async Task <bool> selectExceptUsers()
{
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryTable table = client.GetTable(datasetId, tableId);
string sql = $"SELECT * EXCEPT (SecondNumber) FROM {table}";
BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null);
/*foreach (BigQueryRow row in results)
* {
* Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}");
* }*/
Console.WriteLine(results.ToList().Count);
return(true);
}
public async Task <bool> selectReplaceUsers()
{
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryTable table = client.GetTable(datasetId, tableId);
string sql = $"SELECT * REPLACE (FirstNumber / @divider AS FirstNumber) FROM {table}";
BigQueryParameter[] parameters = new[]
{
new BigQueryParameter("divider", BigQueryDbType.Int64, 2)
};
BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters);
/*foreach (BigQueryRow row in results)
* {
* Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}");
* }*/
Console.WriteLine(results.ToList().Count);
return(true);
}
public async Task <bool> selectNotEqualUsers()
{
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryTable table = client.GetTable(datasetId, tableId);
string sql = $"SELECT FullName, Country ,CreatedAt" +
$"FROM {table}" +
"WHERE Country != 'American Samoa'" +
"ORDER BY Country ASC; ";
BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null);
/*foreach (BigQueryRow row in results)
* {
* Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}");
* }*/
Console.WriteLine(results.ToList().Count);
return(true);
}
public async Task <bool> selectQualifyUsers()
{
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryTable table = client.GetTable(datasetId, tableId);
string sql = $"SELECT FullName, Country, CreatedAt, AvgFirstNumber, MinFirstNumber, TotalFirstNumber" +
"FROM(" +
"SELECT FullName, Country, CreatedAt," +
"AVG(FirstNumber) OVER(PARTITION BY Country) AS AvgFirstNumber," +
"MIN(FirstNumber) OVER(PARTITION BY Country) AS MinFirstNumber," +
"SUM(FirstNumber) OVER(PARTITION BY Country) AS TotalFirstNumber" +
$"FROM {table})" +
"WHERE AvgFirstNumber > 500;";
BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null);
/*foreach (BigQueryRow row in results)
* {
* Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}");
* }*/
Console.WriteLine(results.ToList().Count);
return(true);
}