Details

    • Type: Sub-task
    • Status: Resolved
    • Priority: Major
    • Resolution: Won't Fix
    • Affects Version/s: None
    • Fix Version/s: 0.4.0
    • Component/s: None
    • Labels:
      None

      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"
      }

        Attachments

        1. LineItemTest.java
          2 kB
          Sree Vaddi
        2. lineitem.tbl.1.json
          35 kB
          Sree Vaddi

          Activity

            People

            • Assignee:
              sreevaddi Sree Vaddi
              Reporter:
              sreevaddi Sree Vaddi
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: