全部产品
Search
文档中心

实时数仓Hologres:Hologres查询体验快速入门

更新时间:Aug 20, 2024

本文以一个简单的示例,为您介绍使用Hologres查询外部表和内部表中数据的基本步骤,帮助您直观体验Hologres查询数据性能。

前提条件

背景信息

Hologres在查询数据方面具有极速响应的优势。本示例说明如何创建Hologres的数据库、外部表、内部表以及如何导入数据至内部表中,使用Hologres分别查询内部表和外部表中的数据,帮助您快速体验Hologres查询数据的性能。

操作流程

  1. 步骤一:创建数据库

    通过Hologres快速创建数据库,用于后续存放实例数据进行查询使用。

  2. 步骤二:创建表

    在已创建的数据库中,创建表用于存储示例数据。表分为外部表和内部表:

    • 外部表指在Hologres中不存储数据,只进行字段映射的表。

    • 内部表指数据存储在Hologres中的表。

  3. 步骤三:导入示例数据

    本示例为您提供TPC-H标准数据集数据,您可以通过Hologres将数据导入表中,用于后续查询使用。

  4. 步骤四:查询表中数据

    本示例为您提供了22条标准的TPC-H测试查询SQL语句,帮助您快速体验在Hologres中查询数据的时效性和稳定性。

步骤一:创建数据库

  1. 登录Hologres管理控制台,单击左侧实例列表

  2. 实例列表页面,单击对应实例名称。

  3. 实例详情页左侧导航栏,单击数据库管理

  4. DB授权页面,单击右上角新增数据库

    新增数据库

  5. 新增数据库对话框,配置如下参数。

    新增数据库

    参数

    说明

    实例名

    选择在哪个Hologres实例上创建数据库。默认展示当前已登录实例的名称,您也可以在下拉框中选择其他Hologres实例。

    数据库名称

    本示例数据库名称为tpch_10g。

    说明

    配置的数据库名称必须唯一。

    简单权限策略

    您可以为创建的数据库选择一种权限策略。更多关于权限策略的说明,请参见:

    • SPM:简单权限模型,该权限模型授权是以DB为粒度,划分admin(管理员)、developer(开发者)、writer(读写者)以及viewer(分析师)四种角色,您可以通过少量的权限管理函数,即可对DB中的对象进行方便且安全的权限管理。

    • SLPM:基于Schema级别的简单权限模型,该权限模型以Schema为粒度,划分 <db>.admin(DB管理员)、<db>.<schema>.developer(开发者)、<db>.<schema>.writer(读写者)以及 <db>.<schema>.viewer(分析师),相比于简单权限模型更为细粒度。

    • 专家权限模型:Hologres兼容PostgreSQL,使用与Postgres完全一致的权限系统。

步骤二:创建表

数据库创建成功后,您需在数据库中创建对应的表。创建表时,根据您数据存放的位置不同,分为创建外部表和内部表。

  • 创建外部表

    1. 登录数据库

      1. 在HoloWeb开发页面,单击元数据管理

      2. 元数据管理页面,双击左侧目录树中已创建成功的数据库名称,单击确认登录数据库

    2. 新建外部表

      1. SQL编辑器页面,单击左上角的新增SQL图标。

      2. 在新增的临时Query查询页面,选择已创建的实例名数据库后,请您在SQL查询的编辑框输入如下语句,单击运行

        以下SQL语句用来创建一个映射到MaxCompute公共空间MAXCOMPUTE_PUBLIC_DATA中odps_customer_10g、odps_lineitem_10g等表的外部表,用于后续查询。

        DROP FOREIGN TABLE IF EXISTS odps_customer_10g;
        DROP FOREIGN TABLE IF EXISTS odps_lineitem_10g;
        DROP FOREIGN TABLE IF EXISTS odps_nation_10g;
        DROP FOREIGN TABLE IF EXISTS odps_orders_10g;
        DROP FOREIGN TABLE IF EXISTS odps_part_10g;
        DROP FOREIGN TABLE IF EXISTS odps_partsupp_10g;
        DROP FOREIGN TABLE IF EXISTS odps_region_10g;
        DROP FOREIGN TABLE IF EXISTS odps_supplier_10g;
        
        
        IMPORT FOREIGN SCHEMA "MAXCOMPUTE_PUBLIC_DATA#default" LIMIT to
        (
            odps_customer_10g,
            odps_lineitem_10g,
            odps_nation_10g,
            odps_orders_10g,
            odps_part_10g,
            odps_partsupp_10g,
            odps_region_10g,
            odps_supplier_10g
        ) 
        FROM SERVER odps_server INTO public OPTIONS(if_table_exist'error',if_unsupported_type'error');
  • 创建内部表

    1. 登录数据库

      1. 在HoloWeb开发页面,单击元数据管理

      2. 元数据管理页面,双击左侧目录树中已创建成功的数据库名称,单击确认登录数据库

    2. 新建内部表

      1. SQL编辑器页面,单击左上角的新建SQL窗口

      2. 在新增的临时Query查询页面,选择已创建的实例名数据库后,请您在SQL查询的编辑框输入如下语句,单击运行

        以下SQL语句用来创建名称分别为LINEITEM、ORDERS、PARTSUPP、PART、CUSTOMER、SUPPLIER、NATION和REGION的表,用于后续存储数据。

        DROP TABLE IF EXISTS LINEITEM;
        
        BEGIN;
        CREATE TABLE LINEITEM (
            L_ORDERKEY bigint NOT NULL,
            L_PARTKEY int NOT NULL,
            L_SUPPKEY int NOT NULL,
            L_LINENUMBER int NOT NULL,
            L_QUANTITY DECIMAL(15, 2) NOT NULL,
            L_EXTENDEDPRICE DECIMAL(15, 2) NOT NULL,
            L_DISCOUNT DECIMAL(15, 2) NOT NULL,
            L_TAX DECIMAL(15, 2) NOT NULL,
            L_RETURNFLAG text NOT NULL,
            L_LINESTATUS text NOT NULL,
            L_SHIPDATE date NOT NULL,
            L_COMMITDATE date NOT NULL,
            L_RECEIPTDATE date NOT NULL,
            L_SHIPINSTRUCT text NOT NULL,
            L_SHIPMODE text NOT NULL,
            L_COMMENT text NOT NULL,
            PRIMARY KEY (L_ORDERKEY, L_LINENUMBER)
        );
        CALL set_table_property ('LINEITEM', 'clustering_key', 'L_SHIPDATE,L_ORDERKEY');
        CALL set_table_property ('LINEITEM', 'segment_key', 'L_SHIPDATE');
        CALL set_table_property ('LINEITEM', 'distribution_key', 'L_ORDERKEY');
        CALL set_table_property ('LINEITEM', 'bitmap_columns', 'L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE');
        CALL set_table_property ('LINEITEM', 'dictionary_encoding_columns', 'l_comment:off,l_returnflag,l_linestatus,l_shipinstruct,l_shipmode');
        COMMIT;
        
        DROP TABLE IF EXISTS ORDERS;
        
        BEGIN;
        CREATE TABLE ORDERS (
            O_ORDERKEY bigint NOT NULL PRIMARY KEY,
            O_CUSTKEY int NOT NULL,
            O_ORDERSTATUS text NOT NULL,
            O_TOTALPRICE DECIMAL(15, 2) NOT NULL,
            O_ORDERDATE date NOT NULL,
            O_ORDERPRIORITY text NOT NULL,
            O_CLERK text NOT NULL,
            O_SHIPPRIORITY int NOT NULL,
            O_COMMENT text NOT NULL
        );
        CALL set_table_property ('ORDERS', 'segment_key', 'O_ORDERDATE');
        CALL set_table_property ('ORDERS', 'colocate_with', 'lineitem');
        CALL set_table_property ('ORDERS', 'distribution_key', 'O_ORDERKEY');
        CALL set_table_property ('ORDERS', 'bitmap_columns', 'O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY');
        CALL set_table_property ('ORDERS', 'dictionary_encoding_columns', 'o_comment:off,o_orderstatus,o_orderpriority,o_clerk');
        COMMIT;
        
        DROP TABLE IF EXISTS PARTSUPP;
        
        BEGIN;
        CREATE TABLE PARTSUPP (
            PS_PARTKEY int NOT NULL,
            PS_SUPPKEY int NOT NULL,
            PS_AVAILQTY int NOT NULL,
            PS_SUPPLYCOST DECIMAL(15, 2) NOT NULL,
            PS_COMMENT text NOT NULL,
            PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY)
        );
        CALL set_table_property ('PARTSUPP', 'distribution_key', 'PS_PARTKEY');
        CALL set_table_property ('PARTSUPP', 'colocate_with', 'lineitem');
        CALL set_table_property ('PARTSUPP', 'bitmap_columns', 'ps_availqty');
        CALL set_table_property ('PARTSUPP', 'dictionary_encoding_columns', '');
        COMMIT;
        
        DROP TABLE IF EXISTS PART;
        
        BEGIN;
        CREATE TABLE PART (
            P_PARTKEY int NOT NULL PRIMARY KEY,
            P_NAME text NOT NULL,
            P_MFGR text NOT NULL,
            P_BRAND text NOT NULL,
            P_TYPE text NOT NULL,
            P_SIZE int NOT NULL,
            P_CONTAINER text NOT NULL,
            P_RETAILPRICE DECIMAL(15, 2) NOT NULL,
            P_COMMENT text NOT NULL
        );
        CALL set_table_property ('PART', 'distribution_key', 'P_PARTKEY');
        CALL set_table_property ('PART', 'colocate_with', 'lineitem');
        CALL set_table_property ('PART', 'bitmap_columns', 'P_SIZE,P_MFGR,P_BRAND,P_TYPE,P_CONTAINER');
        CALL set_table_property ('PART', 'dictionary_encoding_columns', 'p_name:off,p_comment:off,p_mfgr,p_brand,p_type,p_container');
        COMMIT;
        
        DROP TABLE IF EXISTS CUSTOMER;
        
        BEGIN;
        CREATE TABLE CUSTOMER (
            C_CUSTKEY int NOT NULL PRIMARY KEY,
            C_NAME text NOT NULL,
            C_ADDRESS text NOT NULL,
            C_NATIONKEY int NOT NULL,
            C_PHONE text NOT NULL,
            C_ACCTBAL DECIMAL(15, 2) NOT NULL,
            C_MKTSEGMENT text NOT NULL,
            C_COMMENT text NOT NULL
        );
        CALL set_table_property ('CUSTOMER', 'distribution_key', 'C_CUSTKEY');
        CALL set_table_property ('CUSTOMER', 'colocate_with', 'lineitem');
        CALL set_table_property ('CUSTOMER', 'bitmap_columns', 'C_NATIONKEY,C_MKTSEGMENT');
        CALL set_table_property ('CUSTOMER', 'dictionary_encoding_columns', 'c_name:off,c_address:off,c_phone:off,c_comment:off,c_mktsegment');
        COMMIT;
        
        DROP TABLE IF EXISTS SUPPLIER;
        
        BEGIN;
        CREATE TABLE SUPPLIER (
            S_SUPPKEY int NOT NULL PRIMARY KEY,
            S_NAME text NOT NULL,
            S_ADDRESS text NOT NULL,
            S_NATIONKEY int NOT NULL,
            S_PHONE text NOT NULL,
            S_ACCTBAL DECIMAL(15, 2) NOT NULL,
            S_COMMENT text NOT NULL
        );
        CALL set_table_property ('SUPPLIER', 'distribution_key', 'S_SUPPKEY');
        CALL set_table_property ('SUPPLIER', 'colocate_with', 'lineitem');
        CALL set_table_property ('SUPPLIER', 'bitmap_columns', 'S_NATIONKEY');
        CALL set_table_property ('SUPPLIER', 'dictionary_encoding_columns', '');
        COMMIT;
        
        DROP TABLE IF EXISTS NATION;
        
        BEGIN;
        CREATE TABLE NATION (
            N_NATIONKEY int NOT NULL PRIMARY KEY,
            N_NAME text NOT NULL,
            N_REGIONKEY int NOT NULL,
            N_COMMENT text NOT NULL
        );
        CALL set_table_property ('NATION', 'distribution_key', 'N_NATIONKEY');
        CALL set_table_property ('NATION', 'colocate_with', 'lineitem');
        CALL set_table_property ('NATION', 'bitmap_columns', '');
        CALL set_table_property ('NATION', 'dictionary_encoding_columns', '');
        COMMIT;
        
        DROP TABLE IF EXISTS REGION;
        
        BEGIN;
        CREATE TABLE REGION (
            R_REGIONKEY int NOT NULL PRIMARY KEY,
            R_NAME text NOT NULL,
            R_COMMENT text
        );
        CALL set_table_property ('REGION', 'distribution_key', 'R_REGIONKEY');
        CALL set_table_property ('REGION', 'colocate_with', 'lineitem');
        CALL set_table_property ('REGION', 'bitmap_columns', '');
        CALL set_table_property ('REGION', 'dictionary_encoding_columns', '');
        COMMIT;

步骤三:导入示例数据

内部表创建成功后,可以通过以下步骤将数据导入Hologres内部表中。

说明

外部表在Hologres中不存储数据,只进行字段映射。通过外部表您可以使用Hologres直接调用存储于MaxCompute公共空间MAXCOMPUTE_PUBLIC_DATA的数据。

  1. SQL编辑器页面,单击左上角的新建SQL窗口

  2. 在新增的临时Query查询页面,选择已创建的实例名数据库后,请您在SQL查询的编辑框输入如下语句,单击运行

    以下SQL语句将MaxCompute公共空间MAXCOMPUTE_PUBLIC_DATA中public.odps_customer_10g、public.odps_lineitem_10g等表中数据导入到对应名称的内部表中,用于后续查询。

    INSERT INTO public.customer SELECT * FROM public.odps_customer_10g ;
    INSERT INTO public.lineitem SELECT * FROM public.odps_lineitem_10g ;
    INSERT INTO public.nation SELECT * FROM public.odps_nation_10g ;
    INSERT INTO public.orders SELECT * FROM public.odps_orders_10g ;
    INSERT INTO public.part SELECT * FROM public.odps_part_10g ;
    INSERT INTO public.partsupp SELECT * FROM public.odps_partsupp_10g ;
    INSERT INTO public.region SELECT * FROM public.odps_region_10g ;
    INSERT INTO public.supplier SELECT * FROM public.odps_supplier_10g ;
    
    vacuum nation;
    vacuum region;
    vacuum supplier;
    vacuum customer;
    vacuum part;
    vacuum partsupp;
    vacuum orders;
    vacuum lineitem;
    
    analyze nation;
    analyze region;
    analyze lineitem;
    analyze orders;
    analyze customer;
    analyze part;
    analyze partsupp;
    analyze supplier;
    analyze lineitem (l_orderkey,l_partkey,l_suppkey);
    analyze orders (o_custkey);
    analyze partsupp(ps_partkey,ps_suppkey);

步骤四:查询表中数据

  1. SQL编辑器页面,单击左上角的新建SQL窗口

  2. 在新增的临时Query查询页面,选择已创建的实例名数据库后,请您在SQL查询的编辑框输入如下语句,单击运行

    说明

    下述SQL代码均为查询内部表数据使用,如需查询外部表,请将对应代码查询的表名更换为外部表名。

    基于TPC-H演化的22条查询语句如下所示,您可以单击表格中的链接进行查看。

    名称

    查询语句

    TPC-H 22条查询语句

    Q1

    Q2

    Q3

    Q4

    Q5

    Q6

    Q7

    Q8

    Q9

    Q10

    Q11

    Q12

    Q13

    Q14

    Q15

    Q16

    Q17

    Q18

    Q19

    Q20

    Q21

    Q22

    -

    -

    • Q1

      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 '120' 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 = 48
              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-23'
              and l_shipdate > date '1995-03-23'
      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-07-01'
              and o_orderdate < date '1996-07-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 = 'EUROPE'
              and o_orderdate >= date '1996-01-01'
              and o_orderdate < date '1996-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 '1996-01-01'
              and l_shipdate < date '1996-01-01' + interval '1' year
              and l_discount between 0.02 - 0.01 and 0.02 + 0.01
              and l_quantity < 24;
    • Q7

      set hg_experimental_enable_double_equivalent=on;
      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 = 'CANADA' and n2.n_name = 'BRAZIL')
                                      or (n1.n_name = 'BRAZIL' and n2.n_name = 'CANADA')
                              )
                              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

      set hg_experimental_enable_double_equivalent=on;
      select
              o_year,
              sum(case
                      when nation = 'BRAZIL' 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 = 'AMERICA'
                              and s_nationkey = n2.n_nationkey
                              and o_orderdate between date '1995-01-01' and date '1996-12-31'
                              and p_type = 'LARGE ANODIZED COPPER'
              ) as all_nations
      group by
              o_year
      order by
              o_year;
    • Q9

      set hg_experimental_enable_double_equivalent=on;
      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 '%maroon%'
              ) 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 '1993-02-01'
              and o_orderdate < date '1993-02-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 = 'EGYPT'
      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 = 'EGYPT'
                      )
      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 ('FOB', 'AIR')
              and l_commitdate < l_receiptdate
              and l_shipdate < l_commitdate
              and l_receiptdate >= date '1997-01-01'
              and l_receiptdate < date '1997-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) as c_count
                      from
                              customer left outer join orders on
                                      c_custkey = o_custkey
                                      and o_comment not like '%special%deposits%'
                      group by
                              c_custkey
              ) c_orders
      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 '1997-06-01'
              and l_shipdate < date '1997-06-01' + interval '1' month;
    • Q15

      with revenue0(SUPPLIER_NO, TOTAL_REVENUE)  as
        (
        select
          l_suppkey,
          sum(l_extendedprice * (1 - l_discount))
        from
          lineitem
        where
          l_shipdate >= date '1995-02-01'
          and l_shipdate < date '1995-02-01' + interval '3' month
        group by
          l_suppkey
        )
      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#45'
              and p_type not like 'SMALL ANODIZED%'
              and p_size in (47, 15, 37, 30, 46, 16, 18, 6)
              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#51'
              and p_container = 'WRAP 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#52'
                      and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
                      and l_quantity >= 3 and l_quantity <= 3 + 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#43'
                      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#52'
                      and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
                      and l_quantity >= 21 and l_quantity <= 21 + 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 'drab%'
                              )
                              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 = 'KENYA'
      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 = 'PERU'
      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
                                      ('24', '32', '17', '18', '12', '14', '22')
                              and c_acctbal > (
                                      select
                                              avg(c_acctbal)
                                      from
                                              customer
                                      where
                                              c_acctbal > 0.00
                                              and substring(c_phone from 1 for 2) in
                                                      ('24', '32', '17', '18', '12', '14', '22')
                              )
                              and not exists (
                                      select
                                              *
                                      from
                                              orders
                                      where
                                              o_custkey = c_custkey
                              )
              ) as custsale
      group by
              cntrycode
      order by
              cntrycode;