AnalyticDB:Query data

Prerequisites

• An AnalyticDB for PostgreSQL database is connected to. For more information, see Connect to a database.

• A sample dataset is loaded to the AnalyticDB for PostgreSQL instance. For more information, see Load a sample dataset.

Procedure

1. Log on to the Data Management (DMS) console and go to the SQLConsole tab of the adb_sampledata_tpch database by performing the following steps:

   1. In the left-side navigation pane, choose Database Instances > Instances Connected.

   2. Click the instance that you want to manage.

   3. Click the adb_sampledata_tpch database.

   4. Double-click the public schema to go to the SQLConsole tab.

   

2. Add the following SQL statements to the SQLConsole tab and click Execute.

   Note The following query statements are referenced from TPC-H. For more information about TPC-H, visit the TPC-H official website.

   -- Q1
   set laser.enable = on;
   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 '93 day'
   group by
       l_returnflag,
       l_linestatus
   order by
       l_returnflag,
       l_linestatus;
   
   -- Q2
   select
       s_acctbal,
       s_name,
       n_name,
       p_partkey,
       p_mfgr,
       s_address,
       s_phone,
       s_comment
   from
       part,
       supplier,
       partsupp,
       nation,
       region
   where
       p_partkey = ps_partkey
       and s_suppkey = ps_suppkey
       and p_size = 23
       and p_type like '%STEEL'
       and s_nationkey = n_nationkey
       and n_regionkey = r_regionkey
       and r_name = 'EUROPE'
       and ps_supplycost = (
           select
               min(ps_supplycost)
           from
               partsupp,
               supplier,
               nation,
               region
           where
               p_partkey = ps_partkey
               and s_suppkey = ps_suppkey
               and s_nationkey = n_nationkey
               and n_regionkey = r_regionkey
               and r_name = 'EUROPE'
       )
   order by
       s_acctbal desc,
       n_name,
       s_name,
       p_partkey
   limit 100;
   
   -- Q3
   select
       l_orderkey,
       sum(l_extendedprice * (1 - l_discount)) as revenue,
       o_orderdate,
       o_shippriority
   from
       customer,
       orders,
       lineitem
   where
       c_mktsegment = 'MACHINERY'
       and c_custkey = o_custkey
       and l_orderkey = o_orderkey
       and o_orderdate < date '1995-03-24'
       and l_shipdate > date '1995-03-24'
   group by
       l_orderkey,
       o_orderdate,
       o_shippriority
   order by
       revenue desc,
       o_orderdate
   limit 10;
   
   -- Q4
   select
       o_orderpriority,
       count(*) as order_count
   from
       orders
   where
       o_orderdate >= date '1996-08-01'
       and o_orderdate < date '1996-08-01' + interval '3' month
       and exists (
           select
               *
           from
               lineitem
           where
               l_orderkey = o_orderkey
               and l_commitdate < l_receiptdate
       )
   group by
       o_orderpriority
   order by
       o_orderpriority;
   
   -- Q5
   select
       n_name,
       sum(l_extendedprice * (1 - l_discount)) as revenue
   from
       customer,
       orders,
       lineitem,
       supplier,
       nation,
       region
   where
       c_custkey = o_custkey
       and l_orderkey = o_orderkey
       and l_suppkey = s_suppkey
       and c_nationkey = s_nationkey
       and s_nationkey = n_nationkey
       and n_regionkey = r_regionkey
       and r_name = 'MIDDLE EAST'
       and o_orderdate >= date '1994-01-01'
       and o_orderdate < date '1994-01-01' + interval '1' year
   group by
       n_name
   order by
       revenue desc;
   
   -- Q6
   select
       sum(l_extendedprice * l_discount) as revenue
   from
       lineitem
   where
       l_shipdate >= date '1994-01-01'
       and l_shipdate < date '1994-01-01' + interval '1' year
       and l_discount between 0.06 - 0.01 and 0.06 + 0.01
       and l_quantity < 24;
   
   -- Q7
   select
       supp_nation,
       cust_nation,
       l_year,
       sum(volume) as revenue
   from
       (
           select
               n1.n_name as supp_nation,
               n2.n_name as cust_nation,
               extract(year from l_shipdate) as l_year,
               l_extendedprice * (1 - l_discount) as volume
           from
               supplier,
               lineitem,
               orders,
               customer,
               nation n1,
               nation n2
           where
               s_suppkey = l_suppkey
               and o_orderkey = l_orderkey
               and c_custkey = o_custkey
               and s_nationkey = n1.n_nationkey
               and c_nationkey = n2.n_nationkey
               and (
                   (n1.n_name = 'JORDAN' and n2.n_name = 'INDONESIA')
                   or (n1.n_name = 'INDONESIA' and n2.n_name = 'JORDAN')
               )
               and l_shipdate between date '1995-01-01' and date '1996-12-31'
       ) as shipping
   group by
       supp_nation,
       cust_nation,
       l_year
   order by
       supp_nation,
       cust_nation,
       l_year;
   
   -- Q8
   select
       o_year,
       sum(case
           when nation = 'INDONESIA' then volume
           else 0
       end) / sum(volume) as mkt_share
   from
       (
           select
               extract(year from o_orderdate) as o_year,
               l_extendedprice * (1 - l_discount) as volume,
               n2.n_name as nation
           from
               part,
               supplier,
               lineitem,
               orders,
               customer,
               nation n1,
               nation n2,
               region
           where
               p_partkey = l_partkey
               and s_suppkey = l_suppkey
               and l_orderkey = o_orderkey
               and o_custkey = c_custkey
               and c_nationkey = n1.n_nationkey
               and n1.n_regionkey = r_regionkey
               and r_name = 'ASIA'
               and s_nationkey = n2.n_nationkey
               and o_orderdate between date '1995-01-01' and date '1996-12-31'
               and p_type = 'STANDARD BRUSHED BRASS'
       ) as all_nations
   group by
       o_year
   order by
       o_year;
   
   -- Q9
   select
       nation,
       o_year,
       sum(amount) as sum_profit
   from
       (
           select
               n_name as nation,
               extract(year from o_orderdate) as o_year,
               l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
           from
               part,
               supplier,
               lineitem,
               partsupp,
               orders,
               nation
           where
               s_suppkey = l_suppkey
               and ps_suppkey = l_suppkey
               and ps_partkey = l_partkey
               and p_partkey = l_partkey
               and o_orderkey = l_orderkey
               and s_nationkey = n_nationkey
               and p_name like '%chartreuse%'
       ) as profit
   group by
       nation,
       o_year
   order by
       nation,
       o_year desc;
   
   -- Q10
   select
       c_custkey,
       c_name,
       sum(l_extendedprice * (1 - l_discount)) as revenue,
       c_acctbal,
       n_name,
       c_address,
       c_phone,
       c_comment
   from
       customer,
       orders,
       lineitem,
       nation
   where
       c_custkey = o_custkey
       and l_orderkey = o_orderkey
       and o_orderdate >= date '1994-08-01'
       and o_orderdate < date '1994-08-01' + interval '3' month
       and l_returnflag = 'R'
       and c_nationkey = n_nationkey
   group by
       c_custkey,
       c_name,
       c_acctbal,
       c_phone,
       n_name,
       c_address,
       c_comment
   order by
       revenue desc
   limit 20;
   
   -- Q11
   select
       ps_partkey,
       sum(ps_supplycost * ps_availqty) as value
   from
       partsupp,
       supplier,
       nation
   where
       ps_suppkey = s_suppkey
       and s_nationkey = n_nationkey
       and n_name = 'INDONESIA'
   group by
       ps_partkey having
           sum(ps_supplycost * ps_availqty) > (
               select
                   sum(ps_supplycost * ps_availqty) * 0.0001000000
               from
                   partsupp,
                   supplier,
                   nation
               where
                   ps_suppkey = s_suppkey
                   and s_nationkey = n_nationkey
                   and n_name = 'INDONESIA'
           )
   order by
       value desc;
   
   -- Q12
   select
       l_shipmode,
       sum(case
           when o_orderpriority = '1-URGENT'
               or o_orderpriority = '2-HIGH'
               then 1
           else 0
       end) as high_line_count,
       sum(case
           when o_orderpriority <> '1-URGENT'
               and o_orderpriority <> '2-HIGH'
               then 1
           else 0
       end) as low_line_count
   from
       orders,
       lineitem
   where
       o_orderkey = l_orderkey
       and l_shipmode in ('REG AIR', 'TRUCK')
       and l_commitdate < l_receiptdate
       and l_shipdate < l_commitdate
       and l_receiptdate >= date '1994-01-01'
       and l_receiptdate < date '1994-01-01' + interval '1' year
   group by
       l_shipmode
   order by
       l_shipmode;
   
   -- Q13
   select
       c_count,
       count(*) as custdist
   from
       (
           select
               c_custkey,
               count(o_orderkey)
           from
               customer left outer join orders on
                   c_custkey = o_custkey
                   and o_comment not like '%pending%requests%'
           group by
               c_custkey
       ) as c_orders (c_custkey, c_count)
   group by
       c_count
   order by
       custdist desc,
       c_count desc;
   
   -- Q14
   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
   from
       lineitem,
       part
   where
       l_partkey = p_partkey
       and l_shipdate >= date '1994-11-01'
       and l_shipdate < date '1994-11-01' + interval '1' month;
   
   -- Q15
   select
       s_suppkey,
       s_name,
       s_address,
       s_phone,
       total_revenue
   from
       supplier,
       revenue0
   where
       s_suppkey = supplier_no
       and total_revenue = (
           select
               max(total_revenue)
           from
               revenue0
       )
   order by
       s_suppkey;
   
   -- Q16
   select
       p_brand,
       p_type,
       p_size,
       count(distinct ps_suppkey) as supplier_cnt
   from
       partsupp,
       part
   where
       p_partkey = ps_partkey
       and p_brand <> 'Brand#44'
       and p_type not like 'SMALL BURNISHED%'
       and p_size in (36, 27, 34, 45, 11, 6, 25, 16)
       and ps_suppkey not in (
           select
               s_suppkey
           from
               supplier
           where
               s_comment like '%Customer%Complaints%'
       )
   group by
       p_brand,
       p_type,
       p_size
   order by
       supplier_cnt desc,
       p_brand,
       p_type,
       p_size;
   
   -- Q17
   select
       sum(l_extendedprice) / 7.0 as avg_yearly
   from
       lineitem,
       part
   where
       p_partkey = l_partkey
       and p_brand = 'Brand#42'
       and p_container = 'JUMBO PACK'
       and l_quantity < (
           select
               0.2 * avg(l_quantity)
           from
               lineitem
           where
               l_partkey = p_partkey
       );
   
   -- Q18
   select
       c_name,
       c_custkey,
       o_orderkey,
       o_orderdate,
       o_totalprice,
       sum(l_quantity)
   from
       customer,
       orders,
       lineitem
   where
       o_orderkey in (
           select
               l_orderkey
           from
               lineitem
           group by
               l_orderkey having
                   sum(l_quantity) > 312
       )
       and c_custkey = o_custkey
       and o_orderkey = l_orderkey
   group by
       c_name,
       c_custkey,
       o_orderkey,
       o_orderdate,
       o_totalprice
   order by
       o_totalprice desc,
       o_orderdate
   limit 100;
   
   -- Q19
   select
       sum(l_extendedprice* (1 - l_discount)) as revenue
   from
       lineitem,
       part
   where
       (
           p_partkey = l_partkey
           and p_brand = 'Brand#43'
           and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
           and l_quantity >= 5 and l_quantity <= 5 + 10
           and p_size between 1 and 5
           and l_shipmode in ('AIR', 'AIR REG')
           and l_shipinstruct = 'DELIVER IN PERSON'
       )
       or
       (
           p_partkey = l_partkey
           and p_brand = 'Brand#45'
           and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
           and l_quantity >= 12 and l_quantity <= 12 + 10
           and p_size between 1 and 10
           and l_shipmode in ('AIR', 'AIR REG')
           and l_shipinstruct = 'DELIVER IN PERSON'
       )
       or
       (
           p_partkey = l_partkey
           and p_brand = 'Brand#11'
           and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
           and l_quantity >= 24 and l_quantity <= 24 + 10
           and p_size between 1 and 15
           and l_shipmode in ('AIR', 'AIR REG')
           and l_shipinstruct = 'DELIVER IN PERSON'
       );
   
   -- Q20
   select
       s_name,
       s_address
   from
       supplier,
       nation
   where
       s_suppkey in (
           select
               ps_suppkey
           from
               partsupp
           where
               ps_partkey in (
                   select
                       p_partkey
                   from
                       part
                   where
                       p_name like 'magenta%'
               )
               and ps_availqty > (
                   select
                       0.5 * sum(l_quantity)
                   from
                       lineitem
                   where
                       l_partkey = ps_partkey
                       and l_suppkey = ps_suppkey
                       and l_shipdate >= date '1996-01-01'
                       and l_shipdate < date '1996-01-01' + interval '1' year
               )
       )
       and s_nationkey = n_nationkey
       and n_name = 'RUSSIA'
   order by
       s_name;
   
   -- Q21
   select
       s_name,
       count(*) as numwait
   from
       supplier,
       lineitem l1,
       orders,
       nation
   where
       s_suppkey = l1.l_suppkey
       and o_orderkey = l1.l_orderkey
       and o_orderstatus = 'F'
       and l1.l_receiptdate > l1.l_commitdate
       and exists (
           select
               *
           from
               lineitem l2
           where
               l2.l_orderkey = l1.l_orderkey
               and l2.l_suppkey <> l1.l_suppkey
       )
       and not exists (
           select
               *
           from
               lineitem l3
           where
               l3.l_orderkey = l1.l_orderkey
               and l3.l_suppkey <> l1.l_suppkey
               and l3.l_receiptdate > l3.l_commitdate
       )
       and s_nationkey = n_nationkey
       and n_name = 'MOZAMBIQUE'
   group by
       s_name
   order by
       numwait desc,
       s_name
   limit 100;
   
   -- Q22
   select
           cntrycode,
           count(*) as numcust,
           sum(c_acctbal) as totacctbal
   from
           (
                   select
                           substring(c_phone from 1 for 2) as cntrycode,
                           c_acctbal
                   from
                           customer
                   where
                           substring(c_phone from 1 for 2) in
                                   ('13', '31', '23', '29', '30', '18', '17')
                           and c_acctbal > (
                                   select
                                           avg(c_acctbal)
                                   from
                                           customer
                                   where
                                           c_acctbal > 0.00
                                           and substring(c_phone from 1 for 2) in
                                                   ('13', '31', '23', '29', '30', '18', '17')
                           )
                           and not exists (
                                   select
                                           *
                                   from
                                           orders
                                   where
                                           o_custkey = c_custkey
                           )
           ) as custsale
   group by
           cntrycode
   order by
           cntrycode;

References

For more information about SQL syntax, see SQL syntax.