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 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]");
}
