You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "Sree Vaddi (JIRA)" <ji...@apache.org> on 2013/07/30 08:09:50 UTC
[jira] [Updated] (DRILL-159) 1.sql
[ https://issues.apache.org/jira/browse/DRILL-159?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sree Vaddi updated DRILL-159:
-----------------------------
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"
}
was:
TPC-H Queries: 1.sql
> 1.sql
> -----
>
> Key: DRILL-159
> URL: https://issues.apache.org/jira/browse/DRILL-159
> Project: Apache Drill
> Issue Type: Sub-task
> Reporter: Sree Vaddi
> Assignee: Sree Vaddi
>
> 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"
> }
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira