public void Advanced1() { var context = new BigQueryContext(); context.From <Natality>() .Where(x => x.year == 2003 && x.state == "OH") .Select(x => new { x.cigarette_use, baby_weight = BqFunc.Average(x.weight_pounds), baby_weight_stdev = BqFunc.StandardDeviation(x.weight_pounds), mother_age = BqFunc.Average(x.mother_age) }) .GroupBy(x => x.cigarette_use) .ToString() .Is(@" SELECT [cigarette_use], AVG([weight_pounds]) AS [baby_weight], STDDEV([weight_pounds]) AS [baby_weight_stdev], AVG([mother_age]) AS [mother_age] FROM [publicdata:samples.natality] WHERE (([year] = 2003) AND ([state] = 'OH')) GROUP BY [cigarette_use] ".TrimSmart()); }
public void Average() { shakespear.Select(x => BqFunc.Average(x.word_count)).ToString() .Is(@" SELECT AVG([word_count]) FROM [publicdata:samples.shakespeare] ".TrimSmart()); }
public void AdvancedExample2_ApproximateBoundingCircleQuery() { Ctx.From <WeatherGeoTable>() .Where(x => x.month == 1) .Select(x => new { distance = (BqFunc.Acos(BqFunc.Sin(39.737567 * BqFunc.PI() / 180) * BqFunc.Sin((x.lat / 1000) * BqFunc.PI() / 180) + BqFunc.Cos(39.737567 * BqFunc.PI() / 180) * BqFunc.Cos((x.lat / 1000) * BqFunc.PI() / 180) * BqFunc.Cos((-104.9847179 - (x.@long / 1000)) * BqFunc.PI() / 180)) * 180 / BqFunc.PI()) * 60 * 1.1515, temp = BqFunc.Average(x.mean_temp), lat = BqFunc.Average(x.lat / 1000), @long = BqFunc.Average(x.@long / 1000) }) .GroupBy(x => x.distance) .Into() .Where(x => x.distance < 100) .Select(x => new { x.distance, x.lat, x.@long, x.temp }) .OrderBy(x => x.distance) .Limit(100) .ToString() .Is(@" SELECT [distance], [lat], [long], [temp] FROM ( SELECT ((((ACOS(((SIN(((39.737567 * PI()) / 180)) * SIN(((([lat] / 1000) * PI()) / 180))) + ((COS(((39.737567 * PI()) / 180)) * COS(((([lat] / 1000) * PI()) / 180))) * COS((((-104.9847179 - ([long] / 1000)) * PI()) / 180))))) * 180) / PI()) * 60) * 1.1515) AS [distance], AVG([mean_temp]) AS [temp], AVG(([lat] / 1000)) AS [lat], AVG(([long] / 1000)) AS [long] FROM [weather_geo.table] WHERE ([month] = 1) GROUP BY [distance] ) WHERE ([distance] < 100) ORDER BY [distance] LIMIT 100 ".TrimSmart()); }
public void AdvancedExample1_BoundingBoxQuery() { Ctx.From <WeatherGeoTable>() .Where(x => x.lat / 1000 > 37.46 && x.lat / 1000 < 37.65 && x.@long / 1000 > -122.5 && x.@long / 1000 < -122.3) .Select(x => new { x.year, x.month, avg_temp = BqFunc.Average(x.mean_temp), min_temp = BqFunc.Min(x.min_temperature), max_temp = BqFunc.Max(x.max_temperature) }) .GroupBy(x => new { x.year, x.month }) .OrderBy(x => x.year) .ThenBy(x => x.month) .ToString() .Is(@" SELECT [year], [month], AVG([mean_temp]) AS [avg_temp], MIN([min_temperature]) AS [min_temp], MAX([max_temperature]) AS [max_temp] FROM [weather_geo.table] WHERE ((((([lat] / 1000) > 37.46) AND (([lat] / 1000) < 37.65)) AND (([long] / 1000) > -122.5)) AND (([long] / 1000) < -122.3)) GROUP BY [year], [month] ORDER BY [year], [month] ".TrimSmart()); }