[jira] [Commented] (DRILL-159) 1.sql

["Jacques Nadeau (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-159?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14011771#comment-14011771 ] 

Jacques Nadeau commented on DRILL-159:
--------------------------------------

Drill now does this automatically, no more need.

> 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
>         Attachments: LineItemTest.java, lineitem.tbl.1.json
>
>
> 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 was sent by Atlassian JIRA
(v6.2#6252)