示例#1
0
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))
示例#2
0
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)
示例#3
0
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)
示例#4
0
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)
示例#5
0
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)
示例#6
0
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)
示例#7
0
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)
示例#8
0
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)
示例#9
0
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)
示例#10
0
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)
示例#11
0
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)
示例#12
0
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)
示例#13
0
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)
示例#14
0
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)
示例#15
0
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)
示例#16
0
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)
示例#17
0
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)
示例#18
0
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)
示例#19
0
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)
示例#20
0
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,)
            )
        """)
    )