def test_distinct_on(sql):
t = symbol('t', discover(sql))
computation = compute(t[['A', 'B']].sort('A').distinct('A'), sql)
assert normalize(str(computation)) == normalize("""
SELECT DISTINCT ON (anon_1."A") anon_1."A", anon_1."B"
FROM (SELECT {tbl}."A" AS "A", {tbl}."B" AS "B"
FROM {tbl}) AS anon_1 ORDER BY anon_1."A" ASC
""".format(tbl=sql.name))
assert odo(computation, tuple) == (('a', 1), ('b', 2))
def test_foreign_key_isin(fkey):
t = symbol('fkey', discover(fkey))
expr = t.sym_id.isin([1, 2])
result = compute(expr, fkey, return_type='native')
expected = """SELECT
fkey.sym_id IN (%(sym_id_1)s, %(sym_id_2)s) AS anon_1
FROM fkey
"""
assert normalize(str(result)) == normalize(expected)
def test_foreign_key_isin(fkey):
t = symbol('fkey', discover(fkey))
expr = t.sym_id.isin([1, 2])
result = compute(expr, fkey)
expected = """SELECT
fkey.sym_id IN (%(sym_id_1)s, %(sym_id_2)s) AS anon_1
FROM fkey
"""
assert normalize(str(result)) == normalize(expected)
def test_foreign_key_chain(fkey):
t = symbol('t', discover(fkey))
expr = t.sym_id.main.data
result = compute(expr, fkey, return_type='native')
expected = """SELECT
main.data
FROM main, fkey, pkey
WHERE fkey.sym_id = pkey.id and pkey.main = main.id
"""
assert normalize(str(result)) == normalize(expected)
def test_auto_join_field(orders):
t = symbol('t', discover(orders))
expr = t.product_id.color
result = compute(expr, orders, return_type='native')
expected = """SELECT
products.color
FROM products, orders
WHERE orders.product_id = products.product_id
"""
assert normalize(str(result)) == normalize(expected)
def test_foreign_key_chain(fkey):
t = symbol('t', discover(fkey))
expr = t.sym_id.main.data
result = compute(expr, fkey)
expected = """SELECT
main.data
FROM main, fkey, pkey
WHERE fkey.sym_id = pkey.id and pkey.main = main.id
"""
assert normalize(str(result)) == normalize(expected)
def test_auto_join_field(orders):
t = symbol('t', discover(orders))
expr = t.product_id.color
result = compute(expr, orders)
expected = """SELECT
products.color
FROM products, orders
WHERE orders.product_id = products.product_id
"""
assert normalize(str(result)) == normalize(expected)
def test_auto_join_projection(orders):
t = symbol('t', discover(orders))
expr = t.product_id[['color', 'price']]
result = compute(expr, orders, return_type='native')
expected = """SELECT
products.color,
products.price
FROM products, orders
WHERE orders.product_id = products.product_id
"""
assert normalize(str(result)) == normalize(expected)
def test_group_by_map(fkey, grouper):
t = symbol('fkey', discover(fkey))
expr = by(t[grouper], id_count=t.size.count())
result = compute(expr, fkey, return_type='native')
expected = """SELECT
fkey.sym_id,
count(fkey.size) AS id_count
FROM fkey
GROUP BY fkey.sym_id
"""
assert normalize(str(result)) == normalize(expected)
def test_group_by_map(fkey, grouper):
t = symbol('fkey', discover(fkey))
expr = by(t[grouper], id_count=t.size.count())
result = compute(expr, fkey)
expected = """SELECT
fkey.sym_id,
count(fkey.size) AS id_count
FROM fkey
GROUP BY fkey.sym_id
"""
assert normalize(str(result)) == normalize(expected)
def test_auto_join_projection(orders):
t = symbol('t', discover(orders))
expr = t.product_id[['color', 'price']]
result = compute(expr, orders)
expected = """SELECT
products.color,
products.price
FROM products, orders
WHERE orders.product_id = products.product_id
"""
assert normalize(str(result)) == normalize(expected)
def test_foreign_key_reduction(orders, products, func):
t = symbol('t', discover(orders))
expr = methodcaller(func)(t.product_id.price)
result = compute(expr, orders, return_type='native')
expected = """WITH alias as (select
products.price as price
from
products, orders
where orders.product_id = products.product_id)
select {0}(alias.price) as price_{0} from alias
""".format(func)
assert normalize(str(result)) == normalize(expected)
def test_foreign_key_group_by(fkey, grouper):
t = symbol('fkey', discover(fkey))
expr = by(t.sym_id[grouper], avg_price=t.sym_id.price.mean())
result = compute(expr, fkey)
expected = """SELECT
pkey.sym,
avg(pkey.price) AS avg_price
FROM pkey, fkey
WHERE fkey.sym_id = pkey.id
GROUP BY pkey.sym
"""
assert normalize(str(result)) == normalize(expected)
def test_foreign_key_reduction(orders, products, func):
t = symbol('t', discover(orders))
expr = methodcaller(func)(t.product_id.price)
result = compute(expr, orders)
expected = """WITH alias as (select
products.price as price
from
products, orders
where orders.product_id = products.product_id)
select {0}(alias.price) as price_{0} from alias
""".format(func)
assert normalize(str(result)) == normalize(expected)
def test_foreign_key_group_by(fkey, grouper):
t = symbol('fkey', discover(fkey))
expr = by(t.sym_id[grouper], avg_price=t.sym_id.price.mean())
result = compute(expr, fkey, return_type='native')
expected = """SELECT
pkey.sym,
avg(pkey.price) AS avg_price
FROM pkey, fkey
WHERE fkey.sym_id = pkey.id
GROUP BY pkey.sym
"""
assert normalize(str(result)) == normalize(expected)
def test_foreign_key_merge_expression(fkey):
from blaze import merge
t = symbol('fkey', discover(fkey))
expr = merge(t.sym_id.sym, t.sym_id.main.data)
expected = """
select pkey.sym, main.data
from
fkey, pkey, main
where
fkey.sym_id = pkey.id and pkey.main = main.id
"""
result = compute(expr, fkey)
assert normalize(str(result)) == normalize(expected)
def test_foreign_key_merge_expression(fkey):
from blaze import merge
t = symbol('fkey', discover(fkey))
expr = merge(t.sym_id.sym, t.sym_id.main.data)
expected = """
select pkey.sym, main.data
from
fkey, pkey, main
where
fkey.sym_id = pkey.id and pkey.main = main.id
"""
result = compute(expr, fkey, return_type='native')
assert normalize(str(result)) == normalize(expected)
def test_agg_sql(db, data):
subset = db.nyc[['pickup_datetime', 'dropoff_datetime', 'passenger_count']]
expr = subset[subset.passenger_count < 4].passenger_count.min()
result = compute(expr, data, return_type='native')
expected = """
select
min(alias.passenger_count) as passenger_count_min
from
(select
nyc.passenger_count as passenger_count
from
nyc
where nyc.passenger_count < %s) as alias
"""
assert normalize(str(result)) == normalize(expected)
def test_agg_sql(db, data):
subset = db.nyc[['pickup_datetime', 'dropoff_datetime', 'passenger_count']]
expr = subset[subset.passenger_count < 4].passenger_count.min()
result = compute(expr, data, return_type='native')
expected = """
select
min(alias.passenger_count) as passenger_count_min
from
(select
nyc.passenger_count as passenger_count
from
nyc
where nyc.passenger_count < % ( passenger_count_1 ) s) as alias
"""
assert normalize(str(result)) == normalize(expected)
def test_str_does_not_repr():
# see GH issue #1240.
d = data(
[('aa', 1), ('b', 2)],
name="ZZZ",
dshape='2 * {a: string, b: int64}',
)
expr = transform(d, c=d.a.str.len() + d.b)
assert (
normalize(str(expr)) ==
normalize("""
Merge(
args=(ZZZ, label(len(_child=ZZZ.a) + ZZZ.b, 'c')),
_varargsexpr=VarArgsExpr(
_inputs=(ZZZ, label(len(_child=ZZZ.a) + ZZZ.b, 'c'))
),
_shape=(2,)
)
""")
)