Example #1
0
def query14():
    randDate = date(helper.rand(1993, 1997), helper.rand(1, 12), 1)
    if db == "duck":
        addDays = str(helper.monthsToDays(randDate, 1))
    elif db == "monet":
        addDays = "interval '1' month"
    if db == "sqlite" or db == "pandas":
        dateIdentifier = ""
        secondDate = f"date('{randDate}', '+1 month')"
    else:
        dateIdentifier = "DATE "
        secondDate = f"DATE '{randDate}' + {addDays}"
    select = "100.00 * SUM(CASE WHEN p_type like 'PROMO%' THEN l_extendedprice*(1-l_discount) ELSE 0 END) / SUM(l_extendedprice * (1 - l_discount)) AS promo_revenue"
    fromTbl = "lineitem, part"
    where = f"l_partkey = p_partkey AND l_shipdate >= {dateIdentifier}'{randDate}' AND l_shipdate < {secondDate}"
    query = f"SELECT {select} FROM {fromTbl} WHERE {where}"

    return query
Example #2
0
def query10():
    randDate = helper.getRandMonth(date(1993, 2, 1), date(1995, 1, 1))
    if db == "duck":
        addDays = str(helper.monthsToDays(randDate, 3))
    elif db == "monet":
        addDays = "interval '3' month"
    if db == "sqlite" or db == "pandas":
        dateIdentifier = ""
        secondDate = f"date('{randDate}', '+3 month')"
    else:
        dateIdentifier = "DATE "
        secondDate = f"DATE '{randDate}' + {addDays}"
    select = "c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment"
    fromTbl = "customer, orders, lineitem, nation"
    where = f"c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate >= {dateIdentifier}'{randDate}' AND o_orderdate < {secondDate} AND l_returnflag = 'R' AND c_nationkey = n_nationkey"
    group = "c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment"
    order = "revenue desc"
    query = f"SELECT {select} FROM {fromTbl} WHERE {where} GROUP BY {group} ORDER BY {order}"

    return query
Example #3
0
def query4():
    randDate = helper.getRandMonth(date(1993, 1, 1), date(1997, 10, 1))
    if db == "duck":
        addDays = str(helper.monthsToDays(randDate, 3))
    elif db == "monet":
        addDays = "interval '3' month"
    if db == "sqlite" or db == "pandas":
        dateIdentifier = ""
        secondDate = f"date('{randDate}', '+3 month')"
    else:
        dateIdentifier = "DATE "
        secondDate = f"DATE '{randDate}' + {addDays}"
    subQuery = f"SELECT * FROM lineitem WHERE l_orderkey = o_orderkey AND l_commitdate < l_receiptdate"
    select = "o_orderpriority, count(*) as order_count"
    fromTbl = "orders"
    where = f"o_orderdate >= {dateIdentifier}'{randDate}' AND o_orderdate < {secondDate} AND exists ({subQuery})"
    group = "o_orderpriority"
    order = "o_orderpriority"
    query = f"SELECT {select} FROM {fromTbl} WHERE {where} GROUP BY {group} ORDER BY {order}"

    return query
Example #4
0
def query15():
    randDate = helper.getRandMonth(date(1993, 1, 1), date(1997, 10, 1))
    if db == "duck":
        addDays = str(helper.monthsToDays(randDate, 3))
    elif db == "monet":
        addDays = "interval '3' month"
    if db == "sqlite" or db == "pandas":
        dateIdentifier = ""
        secondDate = f"date('{randDate}', '+3 month')"
    else:
        dateIdentifier = "DATE "
        secondDate = f"DATE '{randDate}' + {addDays}"
    if db == "pandas":
        view = f"SELECT l_suppkey as supplier_no, sum(l_extendedprice * (1 - l_discount)) as total_revenue FROM lineitem WHERE l_shipdate >= {dateIdentifier}'{randDate}' AND l_shipdate < {secondDate} GROUP BY l_suppkey"
    else:
        view = f"CREATE VIEW revenue (supplier_no, total_revenue) AS SELECT l_suppkey, sum(l_extendedprice * (1 - l_discount)) FROM lineitem WHERE l_shipdate >= {dateIdentifier}'{randDate}' AND l_shipdate < {secondDate} GROUP BY l_suppkey"
    select = "s_suppkey, s_name, s_address, s_phone, total_revenue"
    fromTbl = "supplier, revenue"
    where = "s_suppkey = supplier_no AND total_revenue = (SELECT max(total_revenue) FROM revenue)"
    order = "s_suppkey"
    drop = "DROP VIEW revenue"
    query = f"SELECT {select} FROM {fromTbl} WHERE {where} ORDER BY {order}"

    return (view, query, drop)