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 Json()
{
Ctx.Select(() => new { str = BqFunc.JsonExtract("{\"a\":1, \"b\": [4, 5]}", "$.b") }).ToFlatSql()
.Is(@"SELECT JSON_EXTRACT('{\""a\"":1, \""b\"": [4, 5]}', '$.b') AS [str]");
Ctx.Select(() => new { str = BqFunc.JsonExtractScalar(@"{""a"": [""x"", {""b"":3}]}", "$.a[1].b") }).ToFlatSql()
.Is(@"SELECT JSON_EXTRACT_SCALAR('{\""a\"": [\""x\"", {\""b\"":3}]}', '$.a[1].b') AS [str]");
}
public void Float()
{
Ctx.Select <double?>(() => BqFunc.Float("45.78")).ToFlatSql().Is(@"SELECT FLOAT('45.78')");
Ctx.Select <double?>(() => BqFunc.Float("aaa")).ToFlatSql().Is(@"SELECT FLOAT('aaa')");
Ctx.Select <double?>(() => BqFunc.Float(10)).ToFlatSql().Is(@"SELECT FLOAT(10)");
Ctx.Select <double?>(() => BqFunc.Float((string)null)).ToFlatSql().Is(@"SELECT FLOAT(NULL)");
}
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 Least()
{
var context = new BigQueryContext();
context.Select(() => BqFunc.Least(1, 10000, 100, 20)).ToFlatSql().Is("SELECT LEAST(1, 10000, 100, 20)");
context.Select(() => BqFunc.Least(1.5, 3.54, 2.3, 0.3)).ToFlatSql().Is("SELECT LEAST(1.5, 3.54, 2.3, 0.3)");
}
public void Greatest()
{
var context = new BigQueryContext();
context.Select(() => BqFunc.Greatest(1, 10000, 100, 20)).ToFlatSql().Is("SELECT GREATEST(1, 10000, 100, 20)");
context.Select(() => BqFunc.Greatest(1.5, 3.54, 2.3, 0.3)).ToFlatSql().Is("SELECT GREATEST(1.5, 3.54, 2.3, 0.3)");
}
public void IsInf_Nan()
{
var context = new BigQueryContext();
context.Select(() => BqFunc.IsInfinity(10.5)).ToFlatSql().Is("SELECT IS_INF(10.5)");
context.Select(() => BqFunc.IsNAN(10.5)).ToFlatSql().Is("SELECT IS_NAN(10.5)");
}
public void NotIn_SubQuery2()
{
var context = new BigQueryContext();
context.Select(() => new { value = 100L })
.Into()
.Select(x => new { x.value })
.GroupBy(x => x.value)
.Having(x => (BqFunc.NotIn(x.value, context.From <wikipedia>().Select(y => y.id ?? -1).Limit(1000))))
.ToString()
.Is(@"
SELECT
[value]
FROM
(
SELECT
100 AS [value]
)
GROUP BY
[value]
HAVING
[value] NOT IN
(
SELECT
IFNULL([id], -1)
FROM
[publicdata:samples.wikipedia]
LIMIT 1000
)
".TrimSmart());
}
public void CeilFloorRound()
{
Ctx.Select(() => BqFunc.Ceil(15.123)).ToFlatSql().Is("SELECT CEIL(15.123)");
Ctx.Select(() => BqFunc.Floor(15.123)).ToFlatSql().Is("SELECT FLOOR(15.123)");
Ctx.Select(() => BqFunc.Round(15.123)).ToFlatSql().Is("SELECT ROUND(15.123)");
Ctx.Select(() => BqFunc.Round(15.123, 2)).ToFlatSql().Is("SELECT ROUND(15.123, 2)");
}
public void Advanced2()
{
var context = new BigQueryContext();
context.From <Natality>()
.Where(x => x.state != "")
.Select(x => new
{
x.state,
sex = (x.is_male) ? "Male" : "Female",
cnt = BqFunc.Count()
})
.GroupBy(x => new { x.state, x.sex })
.Having(x => x.cnt > 3000000)
.OrderByDescending(x => x.cnt)
.ToString()
.Is(@"
SELECT
[state],
IF([is_male], 'Male', 'Female') AS [sex],
COUNT(*) AS [cnt]
FROM
[publicdata:samples.natality]
WHERE
([state] != '')
GROUP BY
[state],
[sex]
HAVING
([cnt] > 3000000)
ORDER BY
[cnt] DESC
".TrimSmart());
}
public void Sum()
{
new BigQueryContext().Select(() => new
{
A = 10,
B = 10.5,
C = BqFunc.Timestamp("2013-12-12 01:01:01")
})
.Into()
.Select(x => new
{
S1 = BqFunc.Sum(x.A),
S2 = BqFunc.Sum(x.B),
S3 = BqFunc.Sum(x.C),
}).ToString().Is(@"
SELECT
SUM([A]) AS [S1],
SUM([B]) AS [S2],
SUM([C]) AS [S3]
FROM
(
SELECT
10 AS [A],
10.5 AS [B],
TIMESTAMP('2013-12-12 01:01:01') AS [C]
)
".TrimSmart());
}
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 FromTableQuery()
{
var ctx = new BigQueryContext();
ctx.FromTableQuery <MyClass>("mydata", x => x.table_id.Contains("oo") && BqFunc.Length(x.table_id) >= 4)
.Select(x => new { x.age })
.ToString()
.Is(@"
SELECT
[age]
FROM
TABLE_QUERY([mydata], ""([table_id] CONTAINS 'oo' AND (LENGTH([table_id]) >= 4))"")".TrimSmart());
ctx.FromTableQuery("mydata", new { age = 0 }, x => x.table_id.Contains("oo") && BqFunc.Length(x.table_id) >= 4)
.Select(x => new { x.age })
.ToString()
.Is(@"
SELECT
[age]
FROM
TABLE_QUERY([mydata], ""([table_id] CONTAINS 'oo' AND (LENGTH([table_id]) >= 4))"")".TrimSmart());
// more example
ctx.FromTableQuery <MetaTable>("mydata", x => BqFunc.RegexpMatch(x.table_id, @"^boo[\d]{3,5}"))
.Select(x => new { x.table_id })
.ToString()
.Is(@"
SELECT
[table_id]
FROM
TABLE_QUERY([mydata], ""REGEXP_MATCH([table_id], r'^boo[\d]{3,5}')"")".TrimSmart());
}
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 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 AdvancedExample_2()
{
Ctx.From <Wikipedia>()
.Where(x => (x.contributor_username != "" && x.contributor_username != null) &&
x.timestamp > 1133395200 &&
x.timestamp < 1157068800)
.Select(x => new
{
x.contributor_username,
month = BqFunc.Left(BqFunc.FormatUtcUsec(BqFunc.UtcUsecToMonth(x.timestamp * 1000000)), 7),
total_chars_used = BqFunc.Sum(BqFunc.Length(x.comment))
})
.GroupBy(x => new { x.contributor_username, x.month })
.OrderByDescending(x => x.total_chars_used)
.ToString()
.Is(@"
SELECT
[contributor_username],
LEFT(FORMAT_UTC_USEC(UTC_USEC_TO_MONTH(([timestamp] * 1000000))), 7) AS [month],
SUM(LENGTH([comment])) AS [total_chars_used]
FROM
[publicdata:samples.wikipedia]
WHERE
(((([contributor_username] != '') AND ([contributor_username] IS NOT NULL)) AND ([timestamp] > 1133395200)) AND ([timestamp] < 1157068800))
GROUP BY
[contributor_username],
[month]
ORDER BY
[total_chars_used] DESC
".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 BitCount()
{
var context = new BigQueryContext();
context.Select <long>(() => BqFunc.BitCount(1000)).ToString().TrimFlat()
.Is("SELECT BIT_COUNT(1000)");
}
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 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 In_SubQuery()
{
var context = new BigQueryContext();
context.Select(() => new { value = 100L })
.Into()
.Where(x => (BqFunc.In(x.value, context.From <wikipedia>().Select(y => y.id ?? -1).Limit(1000))))
.Select(x => x.value)
.ToString()
.Is(@"
SELECT
[value]
FROM
(
SELECT
100 AS [value]
)
WHERE
[value] IN
(
SELECT
IFNULL([id], -1)
FROM
[publicdata:samples.wikipedia]
LIMIT 1000
)
".TrimSmart());
}
public void In()
{
var context = new BigQueryContext();
context.Select(() => new { value = 100 })
.Into()
.Select(x => (BqFunc.In(x.value, 10, 20, 50, 1000)) ? 10000 : -10)
.ToString()
.Is(@"
SELECT
IF([value] IN(10, 20, 50, 1000), 10000, -10)
FROM
(
SELECT
100 AS [value]
)
".TrimSmart());
// not in
context.Select(() => new { value = 100 })
.Into()
.Select(x => (!BqFunc.In(x.value, 10, 20, 50, 1000)) ? 10000 : -10)
.ToString()
.Is(@"
SELECT
IF(NOT [value] IN(10, 20, 50, 1000), 10000, -10)
FROM
(
SELECT
100 AS [value]
)
".TrimSmart());
}
public void Integer()
{
Ctx.Select <long?>(() => BqFunc.Integer("45")).ToFlatSql().Is(@"SELECT INTEGER('45')");
Ctx.Select <long?>(() => BqFunc.Integer("45.49")).ToFlatSql().Is(@"SELECT INTEGER('45.49')");
Ctx.Select <long?>(() => BqFunc.Integer(100)).ToFlatSql().Is(@"SELECT INTEGER(100)");
Ctx.Select <long?>(() => BqFunc.Integer(100.32)).ToFlatSql().Is(@"SELECT INTEGER(100.32)");
Ctx.Select <long?>(() => BqFunc.Integer((string)null)).ToFlatSql().Is(@"SELECT INTEGER(NULL)");
}
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 HexString()
{
Ctx.Select <string>(() => BqFunc.HexString(10)).ToFlatSql().Is(@"SELECT HEX_STRING(10)");
Ctx.Select <string>(() => BqFunc.HexString(10.1)).ToFlatSql().Is(@"SELECT HEX_STRING(10.1)");
var d = new Nullable <double>();
Ctx.Select <string>(() => BqFunc.HexString(d.Value)).ToFlatSql().Is(@"SELECT HEX_STRING(NULL)");
}
public void Concat()
{
var j = "Java";
var s = "Script";
var t = "2";
Ctx.Select <string>(() => BqFunc.Concat("Java", "Script", "2")).ToFlatSql().Is(@"SELECT CONCAT('Java', 'Script', '2')");
Ctx.Select <string>(() => j + s + t).ToFlatSql().Is(@"SELECT (('Java' + 'Script') + '2')");
}
public void Cast()
{
Ctx.Select <int>(() => BqFunc.Cast <int>(10)).ToFlatSql().Is(@"SELECT CAST(10 AS INTEGER)");
Ctx.Select <string>(() => BqFunc.Cast <string>(10)).ToFlatSql().Is(@"SELECT CAST(10 AS STRING)");
Ctx.Select <double>(() => BqFunc.Cast <double>(10)).ToFlatSql().Is(@"SELECT CAST(10 AS FLOAT)");
Ctx.Select <bool>(() => BqFunc.Cast <bool>(10)).ToFlatSql().Is(@"SELECT CAST(10 AS BOOLEAN)");
Ctx.Select <DateTimeOffset>(() => BqFunc.Cast <DateTimeOffset>(10)).ToFlatSql().Is(@"SELECT CAST(10 AS TIMESTAMP)");
Ctx.Select <DateTime>(() => BqFunc.Cast <DateTime>(10)).ToFlatSql().Is(@"SELECT CAST(10 AS TIMESTAMP)");
}
public void First()
{
shakespear.Select(x => BqFunc.First(x.word_count)).ToString().Is(@"
SELECT
FIRST([word_count])
FROM
[publicdata:samples.shakespeare]
".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 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());
}
