public void SampleFirstCase() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From <Shakespeare>() .Where(x => x.word.Contains("th")) .Select(x => new { x.word, x.corpus, count = BqFunc.Count(x.word) }) .GroupBy(x => new { x.word, x.corpus }) .ToString() .TrimSmart(); query1.Is(@" SELECT [word], [corpus], COUNT([word]) AS [count] FROM [publicdata:samples.shakespeare] WHERE [word] CONTAINS 'th' GROUP BY [word], [corpus]".TrimSmart()); }
public void Join() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From <Wikipedia>("[publicdata:samples.wikipedia]") .Join(context.From <Wikipedia>("[publicdata:samples.wikipedia]").Select(x => new { x.title, x.wp_namespace }).Limit(1000), (kp, tp) => new { kp, tp }, // alias selector x => x.tp.title == x.kp.title) // conditional .Select(x => new { x.kp.title, x.tp.wp_namespace }) .OrderBy(x => x.title) .ThenByDescending(x => x.wp_namespace) .Limit(100) .IgnoreCase() .ToString(); query1.Is(@" SELECT [kp.title] AS [title], [tp.wp_namespace] AS [wp_namespace] FROM [publicdata:samples.wikipedia] AS [kp] INNER JOIN ( SELECT [title], [wp_namespace] FROM [publicdata:samples.wikipedia] LIMIT 1000 ) AS [tp] ON ([tp.title] = [kp.title]) ORDER BY [title], [wp_namespace] DESC LIMIT 100 IGNORE CASE".TrimSmart()); }
public void Sample1() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From <Wikipedia>() .Where(x => x.wp_namespace == 0) .Select(x => new { x.title, hash_value = BqFunc.Hash(x.title), included_in_sample = (BqFunc.Abs(BqFunc.Hash(x.title)) % 2 == 1) ? "True" : "False" }) .Limit(5) .ToString(); query1.Is(@" SELECT [title], HASH([title]) AS [hash_value], IF(((ABS(HASH([title])) % 2) = 1), 'True', 'False') AS [included_in_sample] FROM [publicdata:samples.wikipedia] WHERE ([wp_namespace] = 0) LIMIT 5".TrimStart()); }
public void DenseRANK() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From<Shakespeare>() .Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10) .Select(x => new { x.word, x.word_count, dense_rank = BqFunc.DenseRank(x) .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query1.Is(@" SELECT [word], [word_count], DENSE_RANK() OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [dense_rank] FROM [publicdata:samples.shakespeare] WHERE (([corpus] = 'othello') AND (LENGTH([word]) > 10)) LIMIT 5 ".TrimSmart()); }
public void WindowFunction() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From <Shakespeare>() .Where(x => x.corpus == "othello") .Select(x => new { x.word, cume_dist = BqFunc.CumulativeDistribution(x) .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query1.Is(@" SELECT [word], CUME_DIST() OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [cume_dist] FROM [publicdata:samples.shakespeare] WHERE ([corpus] = 'othello') LIMIT 5".TrimSmart()); }
public void DescendingOrderingOnMultipleColumns() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From <Shakespeare>() .Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10) .Select(x => new { x.word, x.word_count, row_num = BqFunc.RowNumber(x) .OrderByDescending(y => y.word) .ThenBy(y => y.word_count) .ThenByDescending(y => y.corpus) .Value }) .Limit(5) .ToString(); query1.Is(@" SELECT [word], [word_count], ROW_NUMBER() OVER (ORDER BY [word] DESC, [word_count], [corpus] DESC) AS [row_num] FROM [publicdata:samples.shakespeare] WHERE (([corpus] = 'othello') AND (LENGTH([word]) > 10)) LIMIT 5 ".TrimSmart()); }
public void Join() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From<Wikipedia>("[publicdata:samples.wikipedia]") .Join(context.From<Wikipedia>("[publicdata:samples.wikipedia]").Select(x => new { x.title, x.wp_namespace }).Limit(1000), (kp, tp) => new { kp, tp }, // alias selector x => x.tp.title == x.kp.title) // conditional .Select(x => new { x.kp.title, x.tp.wp_namespace }) .OrderBy(x => x.title) .ThenByDescending(x => x.wp_namespace) .Limit(100) .IgnoreCase() .ToString(); query1.Is(@" SELECT [kp.title] AS [title], [tp.wp_namespace] AS [wp_namespace] FROM [publicdata:samples.wikipedia] AS [kp] INNER JOIN ( SELECT [title], [wp_namespace] FROM [publicdata:samples.wikipedia] LIMIT 1000 ) AS [tp] ON ([tp.title] = [kp.title]) ORDER BY [title], [wp_namespace] DESC LIMIT 100 IGNORE CASE".TrimSmart()); }
public void RowNumber4() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From <Shakespeare>() .Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10) .Select(x => new { x.word, lag = BqFunc.RowNumber(x) .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query1.Is(@" SELECT [word], ROW_NUMBER() OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [lag] FROM [publicdata:samples.shakespeare] WHERE (([corpus] = 'othello') AND (LENGTH([word]) > 10)) LIMIT 5".TrimSmart()); }
public void WithSnapshot() { var context = new BigQuery.Linq.BigQueryContext(); context.From<int>("tablewikipedia") .WithSnapshot() .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@0]".TrimSmart()); context.From<int>("tablewikipedia") .WithSnapshot(TimeSpan.FromHours(1)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@-3600000]".TrimSmart()); context.From<int>("tablewikipedia") .WithSnapshot(new DateTime(2014, 8, 8, 13, 20, 14, DateTimeKind.Utc)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@1407504014000]".TrimSmart()); }
public void WithSnapshot() { var context = new BigQuery.Linq.BigQueryContext(); context.From <int>("tablewikipedia") .WithSnapshot() .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@0]".TrimSmart()); context.From <int>("tablewikipedia") .WithSnapshot(TimeSpan.FromHours(1)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@-3600000]".TrimSmart()); context.From <int>("tablewikipedia") .WithSnapshot(new DateTime(2014, 8, 8, 13, 20, 14, DateTimeKind.Utc)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@1407504014000]".TrimSmart()); }
public void GroupByRollup() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From <natality>() .Where(x => x.year >= 2000 && x.year <= 2002) .Select(x => new { x.year, x.is_male, count = BqFunc.Count(1) }) .GroupBy(x => new { x.year, x.is_male }, rollup: true) .OrderBy(x => x.year) .ThenBy(x => x.is_male) .ToString() .TrimSmart(); query1.Is(@" SELECT [year], [is_male], COUNT(1) AS [count] FROM [publicdata:samples.natality] WHERE (([year] >= 2000) AND ([year] <= 2002)) GROUP BY ROLLUP ( [year], [is_male] ) ORDER BY [year], [is_male]".TrimSmart()); }
public void WindowFunction2() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From <Shakespeare>() .Where(x => x.corpus == "othello") .Select(x => new { x.word, lag = BqFunc.Lag(x, y => y.word, 1, "aaa") .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query1.Is(@" SELECT [word], LAG([word], 1, 'aaa') OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [lag] FROM [publicdata:samples.shakespeare] WHERE ([corpus] = 'othello') LIMIT 5".TrimSmart()); }
public void Percentile() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From <Shakespeare>() .Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10) .Select(x => new { x.word, x.word_count, p_cont = BqFunc.PercentileCont(x, 0.5) .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query1.Is(@" SELECT [word], [word_count], PERCENTILE_CONT(0.5) OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [p_cont] FROM [publicdata:samples.shakespeare] WHERE (([corpus] = 'othello') AND (LENGTH([word]) > 10)) LIMIT 5 ".TrimSmart()); var query2 = context.From <Shakespeare>() .Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10) .Select(x => new { x.word, x.word_count, p_disc = BqFunc.PercentileDisc(x, 0.5) .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query2.Is(@" SELECT [word], [word_count], PERCENTILE_DISC(0.5) OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [p_disc] FROM [publicdata:samples.shakespeare] WHERE (([corpus] = 'othello') AND (LENGTH([word]) > 10)) LIMIT 5 ".TrimSmart()); }
public void Lead() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From <Shakespeare>() .Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10) .Select(x => new { x.word, x.word_count, lead = BqFunc.Lead(x, y => y.word, 1) .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query1.Is(@" SELECT [word], [word_count], LEAD([word], 1) OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [lead] FROM [publicdata:samples.shakespeare] WHERE (([corpus] = 'othello') AND (LENGTH([word]) > 10)) LIMIT 5 ".TrimSmart()); var query2 = context.From <Shakespeare>() .Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10) .Select(x => new { x.word, x.word_count, lead = BqFunc.Lead(x, y => y.word, 1, "defv") .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query2.Is(@" SELECT [word], [word_count], LEAD([word], 1, 'defv') OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [lead] FROM [publicdata:samples.shakespeare] WHERE (([corpus] = 'othello') AND (LENGTH([word]) > 10)) LIMIT 5 ".TrimSmart()); }
public void Lag() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From<Shakespeare>() .Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10) .Select(x => new { x.word, x.word_count, lag = BqFunc.Lag(x, y => y.word, 1) .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query1.Is(@" SELECT [word], [word_count], LAG([word], 1) OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [lag] FROM [publicdata:samples.shakespeare] WHERE (([corpus] = 'othello') AND (LENGTH([word]) > 10)) LIMIT 5 ".TrimSmart()); var query2 = context.From<Shakespeare>() .Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10) .Select(x => new { x.word, x.word_count, lag = BqFunc.Lag(x, y => y.word, 1, "defv") .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query2.Is(@" SELECT [word], [word_count], LAG([word], 1, 'defv') OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [lag] FROM [publicdata:samples.shakespeare] WHERE (([corpus] = 'othello') AND (LENGTH([word]) > 10)) LIMIT 5 ".TrimSmart()); }
public void RealQuery() { var context = new BigQuery.Linq.BigQueryContext(); context.From<int>("tablewikipedia") .WithRange(new DateTime(2014, 8, 8, 13, 20, 14, DateTimeKind.Utc)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@1407504014000-] ".TrimSmart()); }
public void RealEx() { var date = new DateTime(2014,10,30, 3,59,0); var context = new BigQuery.Linq.BigQueryContext(); context.From<int>("tablewikipedia") .WithRange(date.AddSeconds(-10), date) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@1414609130000-1414609140000] ".TrimSmart()); }
public void RealQuery() { var context = new BigQuery.Linq.BigQueryContext(); context.From <int>("tablewikipedia") .WithRange(new DateTime(2014, 8, 8, 13, 20, 14, DateTimeKind.Utc)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@1407504014000-] ".TrimSmart()); }
public void RealEx() { var date = new DateTime(2014, 10, 30, 3, 59, 0, DateTimeKind.Utc); var context = new BigQuery.Linq.BigQueryContext(); context.From <int>("tablewikipedia") .WithRange(date.AddSeconds(-10), date) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@1414641530000-1414641540000] ".TrimSmart()); }
public void DirectSelect() { var context = new BigQuery.Linq.BigQueryContext(); var s = context.Select(() => new { A = "aaa", B = BqFunc.Abs(-5), FROM = 100, }).ToString().TrimEnd(); s.Is(@"SELECT 'aaa' AS [A], ABS(-5) AS [B], 100 AS [FROM]"); }
public void WhereSelect() { var context = new BigQuery.Linq.BigQueryContext(); var s = context.From<Wikipedia>("tablewikipedia") .Where(x => x.wp_namespace == 100) .Select(x => new { x.title, x.wp_namespace }) .ToString().TrimEnd(); s.Is(@"SELECT [title], [wp_namespace] FROM [tablewikipedia] WHERE ([wp_namespace] = 100)"); }
public void WhereSelect() { var context = new BigQuery.Linq.BigQueryContext(); var s = context.From <Wikipedia>("tablewikipedia") .Where(x => x.wp_namespace == 100) .Select(x => new { x.title, x.wp_namespace }) .ToString().TrimEnd(); s.Is(@"SELECT [title], [wp_namespace] FROM [tablewikipedia] WHERE ([wp_namespace] = 100)"); }
public void WithRange() { var context = new BigQuery.Linq.BigQueryContext(); context.From<int>("tablewikipedia") .WithRange(new DateTime(2014, 8, 8, 13, 20, 14, DateTimeKind.Utc)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@1407504014000-]".TrimSmart()); context.From<int>("tablewikipedia") .WithRange(TimeSpan.FromHours(1)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@-3600000-]".TrimSmart()); context.From<int>("tablewikipedia") .WithRange(new DateTime(2012, 10, 1, 2, 3, 4, DateTimeKind.Utc), new DateTime(2014, 8, 8, 13, 20, 14, DateTimeKind.Utc)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@1349056984000-1407504014000]".TrimSmart()); context.From<int>("tablewikipedia") .WithRange(TimeSpan.FromHours(1), TimeSpan.FromHours(2)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@-3600000--7200000]".TrimSmart()); }
public void WithRange() { var context = new BigQuery.Linq.BigQueryContext(); context.From <int>("tablewikipedia") .WithRange(new DateTime(2014, 8, 8, 13, 20, 14, DateTimeKind.Utc)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@1407504014000-]".TrimSmart()); context.From <int>("tablewikipedia") .WithRange(TimeSpan.FromHours(1)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@-3600000-]".TrimSmart()); context.From <int>("tablewikipedia") .WithRange(new DateTime(2012, 10, 1, 2, 3, 4, DateTimeKind.Utc), new DateTime(2014, 8, 8, 13, 20, 14, DateTimeKind.Utc)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@1349056984000-1407504014000]".TrimSmart()); context.From <int>("tablewikipedia") .WithRange(TimeSpan.FromHours(1), TimeSpan.FromHours(2)) .Select() .ToString() .Is(@" SELECT * FROM [tablewikipedia@-3600000--7200000]".TrimSmart()); }
public void WhereWhere() { var context = new BigQuery.Linq.BigQueryContext(); var s = context.From <Wikipedia>("tablewikipedia") .Where(x => x.wp_namespace == 100) .Where(x => x.title != null) .Where(x => x.title == "AiUeo") .Select(x => new { x.title, x.wp_namespace }) .ToString().TrimEnd(); s.Is(@"SELECT [title], [wp_namespace] FROM [tablewikipedia] WHERE ((([wp_namespace] = 100) AND ([title] IS NOT NULL)) AND ([title] = 'AiUeo'))"); }
public void OrderBy() { var context = new BigQuery.Linq.BigQueryContext(); var s = context.From <BigQuery.Linq.Tests.wikipedia>("tablewikipedia") .OrderByDescending(x => x.title) .ThenBy(x => x.wp_namespace) .ThenByDescending(x => x.language) .ThenBy(x => x.revision_id) .Select(x => new { x.title, x.wp_namespace }) .ToString().TrimEnd(); s.Is(@"SELECT [title], [wp_namespace] FROM [tablewikipedia] ORDER BY [title] DESC, [wp_namespace], [language] DESC, [revision_id]"); }
public void GroupByGrouping() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From<natality>() .Where(x => x.year >= 2000 && x.year <= 2002) .Select(x => new { x.year, rollup_year = BqFunc.Grouping(x.year), x.is_male, rollup_gender = BqFunc.Grouping(x.is_male), count = BqFunc.Count(1) }) .GroupBy(x => new { x.year, x.is_male }, rollup: true) .OrderBy(x => x.year) .ThenBy(x => x.is_male) .ToString() .TrimSmart(); query1.Is(@" SELECT [year], GROUPING([year]) AS [rollup_year], [is_male], GROUPING([is_male]) AS [rollup_gender], COUNT(1) AS [count] FROM [publicdata:samples.natality] WHERE (([year] >= 2000) AND ([year] <= 2002)) GROUP BY ROLLUP ( [year], [is_male] ) ORDER BY [year], [is_male]".TrimSmart()); }
public void Percentile() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From<Shakespeare>() .Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10) .Select(x => new { x.word, x.word_count, p_cont = BqFunc.PercentileCont(x, 0.5) .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query1.Is(@" SELECT [word], [word_count], PERCENTILE_CONT(0.5) OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [p_cont] FROM [publicdata:samples.shakespeare] WHERE (([corpus] = 'othello') AND (LENGTH([word]) > 10)) LIMIT 5 ".TrimSmart()); var query2 = context.From<Shakespeare>() .Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10) .Select(x => new { x.word, x.word_count, p_disc = BqFunc.PercentileDisc(x, 0.5) .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query2.Is(@" SELECT [word], [word_count], PERCENTILE_DISC(0.5) OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [p_disc] FROM [publicdata:samples.shakespeare] WHERE (([corpus] = 'othello') AND (LENGTH([word]) > 10)) LIMIT 5 ".TrimSmart()); }
public void WindowFunction() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From<Shakespeare>() .Where(x => x.corpus == "othello") .Select(x => new { x.word, cume_dist = BqFunc.CumulativeDistribution(x) .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query1.Is(@" SELECT [word], CUME_DIST() OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [cume_dist] FROM [publicdata:samples.shakespeare] WHERE ([corpus] = 'othello') LIMIT 5".TrimSmart()); }
public void WindowFunction2() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From<Shakespeare>() .Where(x => x.corpus == "othello") .Select(x => new { x.word, lag = BqFunc.Lag(x, y => y.word, 1, "aaa") .PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query1.Is(@" SELECT [word], LAG([word], 1, 'aaa') OVER (PARTITION BY [corpus] ORDER BY [word_count] DESC) AS [lag] FROM [publicdata:samples.shakespeare] WHERE ([corpus] = 'othello') LIMIT 5".TrimSmart()); }
public void WhereWhere() { var context = new BigQuery.Linq.BigQueryContext(); var s = context.From<Wikipedia>("tablewikipedia") .Where(x => x.wp_namespace == 100) .Where(x => x.title != null) .Where(x => x.title == "AiUeo") .Select(x => new { x.title, x.wp_namespace }) .ToString().TrimEnd(); s.Is(@"SELECT [title], [wp_namespace] FROM [tablewikipedia] WHERE ((([wp_namespace] = 100) AND ([title] IS NOT NULL)) AND ([title] = 'AiUeo'))"); }
public void SampleFirstCase() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From<Shakespeare>() .Where(x => x.word.Contains("th")) .Select(x => new { x.word, x.corpus, count = BqFunc.Count(x.word) }) .GroupBy(x => new { x.word, x.corpus }) .ToString() .TrimSmart(); query1.Is(@" SELECT [word], [corpus], COUNT([word]) AS [count] FROM [publicdata:samples.shakespeare] WHERE [word] CONTAINS 'th' GROUP BY [word], [corpus]".TrimSmart()); }
public void Sample1() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From<Wikipedia>() .Where(x => x.wp_namespace == 0) .Select(x => new { x.title, hash_value = BqFunc.Hash(x.title), included_in_sample = (BqFunc.Abs(BqFunc.Hash(x.title)) % 2 == 1) ? "True" : "False" }) .Limit(5) .ToString(); query1.Is(@" SELECT [title], HASH([title]) AS [hash_value], IF(((ABS(HASH([title])) % 2) = 1), 'True', 'False') AS [included_in_sample] FROM [publicdata:samples.wikipedia] WHERE ([wp_namespace] = 0) LIMIT 5".TrimStart()); }
public void RowNumber3() { var context = new BigQuery.Linq.BigQueryContext(); var query1 = context.From<Shakespeare>() .Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10) .Select(x => new { x.word, lag = BqFunc.RowNumber(x) //.PartitionBy(y => y.corpus) .OrderByDescending(y => y.word_count) .Value }) .Limit(5) .ToString(); query1.Is(@" SELECT [word], ROW_NUMBER() OVER (ORDER BY [word_count] DESC) AS [lag] FROM [publicdata:samples.shakespeare] WHERE (([corpus] = 'othello') AND (LENGTH([word]) > 10)) LIMIT 5".TrimSmart()); }
public void OrderBy() { var context = new BigQuery.Linq.BigQueryContext(); var s = context.From<BigQuery.Linq.Tests.wikipedia>("tablewikipedia") .OrderByDescending(x => x.title) .ThenBy(x => x.wp_namespace) .ThenByDescending(x => x.language) .ThenBy(x => x.revision_id) .Select(x => new { x.title, x.wp_namespace }) .ToString().TrimEnd(); s.Is(@"SELECT [title], [wp_namespace] FROM [tablewikipedia] ORDER BY [title] DESC, [wp_namespace], [language] DESC, [revision_id]"); }