[DRILL-159] 1.sql

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Won't Fix
• Fix Version/s: 0.4.0
• Assignee: Sree Vaddi
• Reporter: Sree Vaddi
• Created: 30/Jul/13 06:04
• Updated: 31/Jul/14 06:33
• Resolved: 28/May/14 22:58

Description

TPC-H Queries: 1.sql

Original Query:
– $ID$
– TPC-H/TPC-R Pricing Summary Report Query (Q1)
– Functional Query Definition
– Approved February 1998
:x
:o
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval ':1' day (3)
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
:n -1

In java:
private static final String sql_1 =
"select "
+ "returnFlag, lineStatus,"
+ "sum(quantity) as sum_qty,"
+ "sum(extendedPrice) as sum_base_price,"
+ "sum(extendedPrice * (1 - discount)) as sum_disc_price,"
+ "sum(extendedPrice * (1 - discount) * (1 + tax)) as sum_charge,"
+ "avg(quantity) as avg_qty,"
+ "avg(extendedPrice) as avg_price,"
+ "avg(discount) as avg_disc,"
+ "count as count_order "
+ "from "
+ "lineitem "
+ "where "
+ "shippingDate <= date '1998-12-01' - interval '" + (60 * Math.random() + 60) + "' day (3) "
+ "group by "
+ "returnFlag,"
+ "lineStatus "
+ "order by "
+ "returnflag,"
+ "lineStatus";

Sample record in json: (first line in lineitem.tbl.1)
{
"orderKey":"1",
"partKey":"155190",
"supplierKey":"7706",
"lineNumber":"1",
"quantity":"17",
"extendedPrice":"21168.23",
"discount":"0.04",
"tax":"0.02",
"returnFlag":"N",
"lineStatus":"O",
"shippingDate":"1996-03-13",
"committedDate":"1996-02-12",
"receivedDate":"1996-03-22",
"shippingInstructions":"DELIVER IN PERSON",
"shippingMode":"TRUCK",
"comment":"egular courts above the"
}