public void Between() { var context = new BigQueryContext(); var x = 1000; context.Select <bool>(() => BqFunc.Between(x, BqFunc.Abs(10), 100)).ToFlatSql() .Is("SELECT (1000 BETWEEN ABS(10) AND 100)"); }
public void Nth() { var context = new BigQueryContext(); var r = context.From <wikipedia>() .Where(x => x.contributor_ip != null) .Select(x => new { clientIpNum = BqFunc.Integer(BqFunc.ParseIP(x.contributor_ip)), classB = BqFunc.Integer(BqFunc.ParseIP(x.contributor_ip) / (256 * 256)) }) .Into() .Join(JoinType.InnerEach, context.From <geolite_city_bq_b2b>(), (a, b) => new { a, b }, x => x.a.classB == x.b.classB) .Where(x => BqFunc.Between(x.a.clientIpNum, x.b.startIpNum, x.b.endIpNum) && x.b.city != "") .Select(x => new { c = BqFunc.Count(), x.b.city, x.b.countryLabel, lat = BqFunc.Nth(1, x.b.latitude), lng = BqFunc.Nth(1, x.b.longitude) }) .GroupBy(x => new { x.city, x.countryLabel }) .OrderByDescending(x => x.c) .ToString(); r.Is(@" SELECT COUNT(*) AS [c], [b.city] AS [city], [b.countryLabel] AS [countryLabel], NTH(1, [b.latitude]) AS [lat], NTH(1, [b.longitude]) AS [lng] FROM ( SELECT INTEGER(PARSE_IP([contributor_ip])) AS [clientIpNum], INTEGER((PARSE_IP([contributor_ip]) / 65536)) AS [classB] FROM [publicdata:samples.wikipedia] WHERE ([contributor_ip] IS NOT NULL) ) AS [a] INNER JOIN EACH [fh-bigquery:geocode.geolite_city_bq_b2b] AS [b] ON ([a.classB] = [b.classB]) WHERE (([a.clientIpNum] BETWEEN [b.startIpNum] AND [b.endIpNum]) AND ([b.city] != '')) GROUP BY [city], [countryLabel] ORDER BY [c] DESC ".TrimSmart()); }