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());
}
