Doris【部署 02】Linux情况Doris数据库进行SSB基准测试

嚴華  论坛元老 | 2024-6-21 13:07:22 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1051|帖子 1051|积分 3153

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要 登录 才可以下载或查看，没有账号？立即注册

x
Star Schema Benchmark(SSB) 是一个轻量级的数仓场景下的性能测试集。SSB 基于 TPC-H 提供了一个简化版的星型模子数据集，主要用于测试在星型模子下，多表关联查询的性能表现。别的，业界内通常也会将 SSB 打平为宽表模子（以下简称：SSB flat），来测试查询引擎的性能，参考Clickhouse。

  
1.情况准备

请先参照 官方文档 进行 Apache Doris 的安装部署，以得到一个正常运行中的 Doris 集群（至少包罗 1 FE 1 BE，推荐 1 FE 3 BE）。
2.数据准备

2.1 下载安装 SSB 数据生成工具

执行以下脚本下载并编译 ssb-tools 工具
  1. sh bin/build-ssb-dbgen.sh
复制代码
安装乐成后，将在 ssb-dbgen/ 目次下生成 dbgen 二进制文件。
2.2 生成 SSB 测试集

执行以下脚本生成 SSB 数据集：
  1. sh bin/gen-ssb-data.sh -s 100
复制代码


  • 注1：通过 sh gen-ssb-data.sh -h 查看脚本资助。
  • 注2：数据会以 .tbl 为后缀生成在 ssb-data/ 目次下。文件总大小约 600GB。生成时间可能在数分钟到1小时不等。
  • 注3：默认生成 100G 的标准测试数据集
测试：
  1. # 生成命令
  2. sh bin/gen-ssb-data.sh -s 1
  4. # 生成数据
  5. 2.8M    /opt/ssb/ssb-tools/bin/ssb-data/customer.tbl
  6. 228K    /opt/ssb/ssb-tools/bin/ssb-data/date.tbl
  7. 57M     /opt/ssb/ssb-tools/bin/ssb-data/lineorder.tbl.1
  8. 58M     /opt/ssb/ssb-tools/bin/ssb-data/lineorder.tbl.10
  9. 57M     /opt/ssb/ssb-tools/bin/ssb-data/lineorder.tbl.2
  10. 58M     /opt/ssb/ssb-tools/bin/ssb-data/lineorder.tbl.3
  11. 58M     /opt/ssb/ssb-tools/bin/ssb-data/lineorder.tbl.4
  12. 58M     /opt/ssb/ssb-tools/bin/ssb-data/lineorder.tbl.5
  13. 58M     /opt/ssb/ssb-tools/bin/ssb-data/lineorder.tbl.6
  14. 58M     /opt/ssb/ssb-tools/bin/ssb-data/lineorder.tbl.7
  15. 58M     /opt/ssb/ssb-tools/bin/ssb-data/lineorder.tbl.8
  16. 58M     /opt/ssb/ssb-tools/bin/ssb-data/lineorder.tbl.9
  17. 17M     /opt/ssb/ssb-tools/bin/ssb-data/part.tbl
  18. 164K    /opt/ssb/ssb-tools/bin/ssb-data/supplier.tbl
复制代码
2.3 建表

2.3.1 准备 doris-cluster.conf 文件

在调用导入脚本前，需要将 FE 的 ip 端口等信息写在 doris-cluster.conf 文件中。文件位置在 ${DORIS_HOME}/tools/ssb-tools/conf/ 目次下。文件内容包罗 FE 的 ip，HTTP 端口，用户名，密码以及待导入数据的 DB 名称：
  1. # Any of FE host
  2. export FE_HOST='127.0.0.1'
  3. # http_port in fe.conf
  4. export FE_HTTP_PORT=8030
  5. # query_port in fe.conf
  6. export FE_QUERY_PORT=9030
  7. # Doris username
  8. export USER='root'
  9. # Doris password
  10. export PASSWORD=''
  11. # The database where SSB tables located
  12. export DB='ssb'
复制代码
2.3.2 执行以下脚本生成创建 SSB 表

  1. sh bin/create-ssb-tables.sh -s 10
复制代码
或者复制 create-ssb-tables.sql 和 create-ssb-flat-table.sql 中的建表语句，在 MySQL 客户端中执行。
  1. CREATE TABLE IF NOT EXISTS `lineorder` (
  2.   `lo_orderkey` bigint(20) NOT NULL COMMENT "",
  3.   `lo_linenumber` int(11) NOT NULL COMMENT "",
  4.   `lo_custkey` int(11) NOT NULL COMMENT "",
  5.   `lo_partkey` int(11) NOT NULL COMMENT "",
  6.   `lo_suppkey` int(11) NOT NULL COMMENT "",
  7.   `lo_orderdate` int(11) NOT NULL COMMENT "",
  8.   `lo_orderpriority` varchar(16) NOT NULL COMMENT "",
  9.   `lo_shippriority` int(11) NOT NULL COMMENT "",
  10.   `lo_quantity` int(11) NOT NULL COMMENT "",
  11.   `lo_extendedprice` int(11) NOT NULL COMMENT "",
  12.   `lo_ordtotalprice` int(11) NOT NULL COMMENT "",
  13.   `lo_discount` int(11) NOT NULL COMMENT "",
  14.   `lo_revenue` int(11) NOT NULL COMMENT "",
  15.   `lo_supplycost` int(11) NOT NULL COMMENT "",
  16.   `lo_tax` int(11) NOT NULL COMMENT "",
  17.   `lo_commitdate` int(11) NOT NULL COMMENT "",
  18.   `lo_shipmode` varchar(11) NOT NULL COMMENT ""
  19. ) ENGINE=OLAP
  20. DUPLICATE KEY(`lo_orderkey`)
  21. COMMENT "OLAP"
  22. PARTITION BY RANGE(`lo_orderdate`)
  23. (
  24. PARTITION p1 VALUES [("-2147483648"), ("19930101")),
  25. PARTITION p2 VALUES [("19930101"), ("19940101")),
  26. PARTITION p3 VALUES [("19940101"), ("19950101")),
  27. PARTITION p4 VALUES [("19950101"), ("19960101")),
  28. PARTITION p5 VALUES [("19960101"), ("19970101")),
  29. PARTITION p6 VALUES [("19970101"), ("19980101")),
  30. PARTITION p7 VALUES [("19980101"), ("19990101"))
  31. )
  32. DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 120
  33. PROPERTIES (
  34.   "replication_num" = "1",
  35.   "colocate_with" = "groupa1"
  36. );
  38. CREATE TABLE IF NOT EXISTS `customer` (
  39.   `c_custkey` int(11) NOT NULL COMMENT "",
  40.   `c_name` varchar(26) NOT NULL COMMENT "",
  41.   `c_address` varchar(41) NOT NULL COMMENT "",
  42.   `c_city` varchar(11) NOT NULL COMMENT "",
  43.   `c_nation` varchar(16) NOT NULL COMMENT "",
  44.   `c_region` varchar(13) NOT NULL COMMENT "",
  45.   `c_phone` varchar(16) NOT NULL COMMENT "",
  46.   `c_mktsegment` varchar(11) NOT NULL COMMENT ""
  47. ) ENGINE=OLAP
  48. DUPLICATE KEY(`c_custkey`)
  49. COMMENT "OLAP"
  50. DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 12
  51. PROPERTIES (
  52.   "replication_num" = "1",
  53.   "colocate_with" = "groupa2"
  54. );
  56. CREATE TABLE IF NOT EXISTS `dates` (
  57.   `d_datekey` int(11) NOT NULL COMMENT "",
  58.   `d_date` varchar(20) NOT NULL COMMENT "",
  59.   `d_dayofweek` varchar(10) NOT NULL COMMENT "",
  60.   `d_month` varchar(11) NOT NULL COMMENT "",
  61.   `d_year` int(11) NOT NULL COMMENT "",
  62.   `d_yearmonthnum` int(11) NOT NULL COMMENT "",
  63.   `d_yearmonth` varchar(9) NOT NULL COMMENT "",
  64.   `d_daynuminweek` int(11) NOT NULL COMMENT "",
  65.   `d_daynuminmonth` int(11) NOT NULL COMMENT "",
  66.   `d_daynuminyear` int(11) NOT NULL COMMENT "",
  67.   `d_monthnuminyear` int(11) NOT NULL COMMENT "",
  68.   `d_weeknuminyear` int(11) NOT NULL COMMENT "",
  69.   `d_sellingseason` varchar(14) NOT NULL COMMENT "",
  70.   `d_lastdayinweekfl` int(11) NOT NULL COMMENT "",
  71.   `d_lastdayinmonthfl` int(11) NOT NULL COMMENT "",
  72.   `d_holidayfl` int(11) NOT NULL COMMENT "",
  73.   `d_weekdayfl` int(11) NOT NULL COMMENT ""
  74. ) ENGINE=OLAP
  75. DUPLICATE KEY(`d_datekey`)
  76. COMMENT "OLAP"
  77. DISTRIBUTED BY HASH(`d_datekey`) BUCKETS 1
  78. PROPERTIES (
  79.   "replication_num" = "1",
  80.   "colocate_with" = "groupa3"
  81. );
  83. CREATE TABLE IF NOT EXISTS `supplier` (
  84.   `s_suppkey` int(11) NOT NULL COMMENT "",
  85.   `s_name` varchar(26) NOT NULL COMMENT "",
  86.   `s_address` varchar(26) NOT NULL COMMENT "",
  87.   `s_city` varchar(11) NOT NULL COMMENT "",
  88.   `s_nation` varchar(16) NOT NULL COMMENT "",
  89.   `s_region` varchar(13) NOT NULL COMMENT "",
  90.   `s_phone` varchar(16) NOT NULL COMMENT ""
  91. ) ENGINE=OLAP
  92. DUPLICATE KEY(`s_suppkey`)
  93. COMMENT "OLAP"
  94. DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 12
  95. PROPERTIES (
  96.   "replication_num" = "1",
  97.   "colocate_with" = "groupa4"
  98. );
  100. CREATE TABLE IF NOT EXISTS `part` (
  101.   `p_partkey` int(11) NOT NULL COMMENT "",
  102.   `p_name` varchar(23) NOT NULL COMMENT "",
  103.   `p_mfgr` varchar(7) NOT NULL COMMENT "",
  104.   `p_category` varchar(8) NOT NULL COMMENT "",
  105.   `p_brand` varchar(10) NOT NULL COMMENT "",
  106.   `p_color` varchar(12) NOT NULL COMMENT "",
  107.   `p_type` varchar(26) NOT NULL COMMENT "",
  108.   `p_size` int(11) NOT NULL COMMENT "",
  109.   `p_container` varchar(11) NOT NULL COMMENT ""
  110. ) ENGINE=OLAP
  111. DUPLICATE KEY(`p_partkey`)
  112. COMMENT "OLAP"
  113. DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 12
  114. PROPERTIES (
  115.   "replication_num" = "1",
  116.   "colocate_with" = "groupa5"
  117. );
复制代码
  1. CREATE TABLE IF NOT EXISTS `lineorder_flat` (
  2.   `LO_ORDERDATE` int(11) NOT NULL COMMENT "",
  3.   `LO_ORDERKEY` bigint(20) NOT NULL COMMENT "",
  4.   `LO_LINENUMBER` tinyint(4) NOT NULL COMMENT "",
  5.   `LO_CUSTKEY` int(11) NOT NULL COMMENT "",
  6.   `LO_PARTKEY` int(11) NOT NULL COMMENT "",
  7.   `LO_SUPPKEY` int(11) NOT NULL COMMENT "",
  8.   `LO_ORDERPRIORITY` varchar(100) NOT NULL COMMENT "",
  9.   `LO_SHIPPRIORITY` tinyint(4) NOT NULL COMMENT "",
  10.   `LO_QUANTITY` tinyint(4) NOT NULL COMMENT "",
  11.   `LO_EXTENDEDPRICE` int(11) NOT NULL COMMENT "",
  12.   `LO_ORDTOTALPRICE` int(11) NOT NULL COMMENT "",
  13.   `LO_DISCOUNT` tinyint(4) NOT NULL COMMENT "",
  14.   `LO_REVENUE` int(11) NOT NULL COMMENT "",
  15.   `LO_SUPPLYCOST` int(11) NOT NULL COMMENT "",
  16.   `LO_TAX` tinyint(4) NOT NULL COMMENT "",
  17.   `LO_COMMITDATE` date NOT NULL COMMENT "",
  18.   `LO_SHIPMODE` varchar(100) NOT NULL COMMENT "",
  19.   `C_NAME` varchar(100) NOT NULL COMMENT "",
  20.   `C_ADDRESS` varchar(100) NOT NULL COMMENT "",
  21.   `C_CITY` varchar(100) NOT NULL COMMENT "",
  22.   `C_NATION` varchar(100) NOT NULL COMMENT "",
  23.   `C_REGION` varchar(100) NOT NULL COMMENT "",
  24.   `C_PHONE` varchar(100) NOT NULL COMMENT "",
  25.   `C_MKTSEGMENT` varchar(100) NOT NULL COMMENT "",
  26.   `S_NAME` varchar(100) NOT NULL COMMENT "",
  27.   `S_ADDRESS` varchar(100) NOT NULL COMMENT "",
  28.   `S_CITY` varchar(100) NOT NULL COMMENT "",
  29.   `S_NATION` varchar(100) NOT NULL COMMENT "",
  30.   `S_REGION` varchar(100) NOT NULL COMMENT "",
  31.   `S_PHONE` varchar(100) NOT NULL COMMENT "",
  32.   `P_NAME` varchar(100) NOT NULL COMMENT "",
  33.   `P_MFGR` varchar(100) NOT NULL COMMENT "",
  34.   `P_CATEGORY` varchar(100) NOT NULL COMMENT "",
  35.   `P_BRAND` varchar(100) NOT NULL COMMENT "",
  36.   `P_COLOR` varchar(100) NOT NULL COMMENT "",
  37.   `P_TYPE` varchar(100) NOT NULL COMMENT "",
  38.   `P_SIZE` tinyint(4) NOT NULL COMMENT "",
  39.   `P_CONTAINER` varchar(100) NOT NULL COMMENT ""
  40. ) ENGINE=OLAP
  41. DUPLICATE KEY(`LO_ORDERDATE`, `LO_ORDERKEY`)
  42. COMMENT "OLAP"
  43. PARTITION BY RANGE(`LO_ORDERDATE`)
  44. (
  45. PARTITION p1992 VALUES [("-2147483648"), ("19930101")),
  46. PARTITION p1993 VALUES [("19930101"), ("19940101")),
  47. PARTITION p1994 VALUES [("19940101"), ("19950101")),
  48. PARTITION p1995 VALUES [("19950101"), ("19960101")),
  49. PARTITION p1996 VALUES [("19960101"), ("19970101")),
  50. PARTITION p1997 VALUES [("19970101"), ("19980101")),
  51. PARTITION p1998 VALUES [("19980101"), ("19990101"))
  52. )
  53. DISTRIBUTED BY HASH(`LO_ORDERKEY`) BUCKETS 120
  54. PROPERTIES (
  55.   "replication_num" = "1",
  56.   "colocate_with" = "groupxx1"
  57. );
复制代码
2.4 导入数据

使用以下下令完成 SSB 测试集全部数据导入及 SSB FLAT 宽表数据合成并导入到表里：
  1. sh bin/load-ssb-data.sh
复制代码
2.5 查抄导入数据

  1. select count(*) from part;
  2. select count(*) from customer;
  3. select count(*) from supplier;
  4. select count(*) from dates;
  5. select count(*) from lineorder;
  6. select count(*) from lineorder_flat;
复制代码
2.6 查询测试

SSB-FlAT 查询语句：ssb-flat-queries
标准 SSB 查询语句：ssb-queries
2.6.1 SSB FLAT 测试 SQL

  1. --Q1.1
  2. SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
  3. FROM lineorder_flat
  4. WHERE
  5.     LO_ORDERDATE >= 19930101
  6.     AND LO_ORDERDATE <= 19931231
  7.     AND LO_DISCOUNT BETWEEN 1 AND 3
  8.     AND LO_QUANTITY < 25;
  10. --Q1.2
  11. SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
  12. FROM lineorder_flat
  13. WHERE
  14.     LO_ORDERDATE >= 19940101
  15.   AND LO_ORDERDATE <= 19940131
  16.   AND LO_DISCOUNT BETWEEN 4 AND 6
  17.   AND LO_QUANTITY BETWEEN 26 AND 35;
  19. --Q1.3
  20. SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
  21. FROM lineorder_flat
  22. WHERE
  23.     weekofyear(LO_ORDERDATE) = 6
  24.   AND LO_ORDERDATE >= 19940101
  25.   AND LO_ORDERDATE <= 19941231
  26.   AND LO_DISCOUNT BETWEEN 5 AND 7
  27.   AND LO_QUANTITY BETWEEN 26 AND 35;
  29. --Q2.1
  30. SELECT
  31.     SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR,
  32.     P_BRAND
  33. FROM lineorder_flat
  34. WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
  35. GROUP BY YEAR, P_BRAND
  36. ORDER BY YEAR, P_BRAND;
  38. --Q2.2
  39. SELECT
  40.     SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR,
  41.     P_BRAND
  42. FROM lineorder_flat
  43. WHERE
  44.     P_BRAND >= 'MFGR#2221'
  45.   AND P_BRAND <= 'MFGR#2228'
  46.   AND S_REGION = 'ASIA'
  47. GROUP BY YEAR, P_BRAND
  48. ORDER BY YEAR, P_BRAND;
  50. --Q2.3
  51. SELECT
  52.     SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR,
  53.     P_BRAND
  54. FROM lineorder_flat
  55. WHERE
  56.     P_BRAND = 'MFGR#2239'
  57.   AND S_REGION = 'EUROPE'
  58. GROUP BY YEAR, P_BRAND
  59. ORDER BY YEAR, P_BRAND;
  61. --Q3.1
  62. SELECT
  63.     C_NATION,
  64.     S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR,
  65.     SUM(LO_REVENUE) AS revenue
  66. FROM lineorder_flat
  67. WHERE
  68.     C_REGION = 'ASIA'
  69.   AND S_REGION = 'ASIA'
  70.   AND LO_ORDERDATE >= 19920101
  71.   AND LO_ORDERDATE <= 19971231
  72. GROUP BY C_NATION, S_NATION, YEAR
  73. ORDER BY YEAR ASC, revenue DESC;
  75. --Q3.2
  76. SELECT
  77.     C_CITY,
  78.     S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR,
  79.     SUM(LO_REVENUE) AS revenue
  80. FROM lineorder_flat
  81. WHERE
  82.     C_NATION = 'UNITED STATES'
  83.   AND S_NATION = 'UNITED STATES'
  84.   AND LO_ORDERDATE >= 19920101
  85.   AND LO_ORDERDATE <= 19971231
  86. GROUP BY C_CITY, S_CITY, YEAR
  87. ORDER BY YEAR ASC, revenue DESC;
  89. --Q3.3
  90. SELECT
  91.     C_CITY,
  92.     S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR,
  93.     SUM(LO_REVENUE) AS revenue
  94. FROM lineorder_flat
  95. WHERE
  96.     C_CITY IN ('UNITED KI1', 'UNITED KI5')
  97.   AND S_CITY IN ('UNITED KI1', 'UNITED KI5')
  98.   AND LO_ORDERDATE >= 19920101
  99.   AND LO_ORDERDATE <= 19971231
  100. GROUP BY C_CITY, S_CITY, YEAR
  101. ORDER BY YEAR ASC, revenue DESC;
  103. --Q3.4
  104. SELECT
  105.     C_CITY,
  106.     S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR,
  107.     SUM(LO_REVENUE) AS revenue
  108. FROM lineorder_flat
  109. WHERE
  110.     C_CITY IN ('UNITED KI1', 'UNITED KI5')
  111.   AND S_CITY IN ('UNITED KI1', 'UNITED KI5')
  112.   AND LO_ORDERDATE >= 19971201
  113.   AND LO_ORDERDATE <= 19971231
  114. GROUP BY C_CITY, S_CITY, YEAR
  115. ORDER BY YEAR ASC, revenue DESC;
  117. --Q4.1
  118. SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
  119.     C_NATION,
  120.     SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
  121. FROM lineorder_flat
  122. WHERE
  123.     C_REGION = 'AMERICA'
  124.   AND S_REGION = 'AMERICA'
  125.   AND P_MFGR IN ('MFGR#1', 'MFGR#2')
  126. GROUP BY YEAR, C_NATION
  127. ORDER BY YEAR ASC, C_NATION ASC;
  129. --Q4.2
  130. SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
  131.     S_NATION,
  132.     P_CATEGORY,
  133.     SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
  134. FROM lineorder_flat
  135. WHERE
  136.     C_REGION = 'AMERICA'
  137.   AND S_REGION = 'AMERICA'
  138.   AND LO_ORDERDATE >= 19970101
  139.   AND LO_ORDERDATE <= 19981231
  140.   AND P_MFGR IN ('MFGR#1', 'MFGR#2')
  141. GROUP BY YEAR, S_NATION, P_CATEGORY
  142. ORDER BY
  143.     YEAR ASC,
  144.     S_NATION ASC,
  145.     P_CATEGORY ASC;
  147. --Q4.3
  148. SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
  149.     S_CITY,
  150.     P_BRAND,
  151.     SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
  152. FROM lineorder_flat
  153. WHERE
  154.     S_NATION = 'UNITED STATES'
  155.   AND LO_ORDERDATE >= 19970101
  156.   AND LO_ORDERDATE <= 19981231
  157.   AND P_CATEGORY = 'MFGR#14'
  158. GROUP BY YEAR, S_CITY, P_BRAND
  159. ORDER BY YEAR ASC, S_CITY ASC, P_BRAND ASC;
复制代码
2.6.2 SSB 标准测试 SQL

  1. --Q1.1
  2. SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
  3. FROM lineorder, dates
  4. WHERE
  5.     lo_orderdate = d_datekey
  6.   AND d_year = 1993
  7.   AND lo_discount BETWEEN 1 AND 3
  8.   AND lo_quantity < 25;
  10. --Q1.2
  11. SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
  12. FROM lineorder, dates
  13. WHERE
  14.     lo_orderdate = d_datekey
  15.   AND d_yearmonth = 'Jan1994'
  16.   AND lo_discount BETWEEN 4 AND 6
  17.   AND lo_quantity BETWEEN 26 AND 35;
  18.    
  19. --Q1.3
  20. SELECT
  21.     SUM(lo_extendedprice * lo_discount) AS REVENUE
  22. FROM lineorder, dates
  23. WHERE
  24.     lo_orderdate = d_datekey
  25.   AND d_weeknuminyear = 6
  26.   AND d_year = 1994
  27.   AND lo_discount BETWEEN 5 AND 7
  28.   AND lo_quantity BETWEEN 26 AND 35;
  29.    
  30. --Q2.1
  31. SELECT SUM(lo_revenue), d_year, p_brand
  32. FROM lineorder, dates, part, supplier
  33. WHERE
  34.     lo_orderdate = d_datekey
  35.   AND lo_partkey = p_partkey
  36.   AND lo_suppkey = s_suppkey
  37.   AND p_category = 'MFGR#12'
  38.   AND s_region = 'AMERICA'
  39. GROUP BY d_year, p_brand
  40. ORDER BY p_brand;
  42. --Q2.2
  43. SELECT SUM(lo_revenue), d_year, p_brand
  44. FROM lineorder, dates, part, supplier
  45. WHERE
  46.     lo_orderdate = d_datekey
  47.   AND lo_partkey = p_partkey
  48.   AND lo_suppkey = s_suppkey
  49.   AND p_brand BETWEEN 'MFGR#2221' AND 'MFGR#2228'
  50.   AND s_region = 'ASIA'
  51. GROUP BY d_year, p_brand
  52. ORDER BY d_year, p_brand;
  54. --Q2.3
  55. SELECT SUM(lo_revenue), d_year, p_brand
  56. FROM lineorder, dates, part, supplier
  57. WHERE
  58.     lo_orderdate = d_datekey
  59.   AND lo_partkey = p_partkey
  60.   AND lo_suppkey = s_suppkey
  61.   AND p_brand = 'MFGR#2239'
  62.   AND s_region = 'EUROPE'
  63. GROUP BY d_year, p_brand
  64. ORDER BY d_year, p_brand;
  66. --Q3.1
  67. SELECT
  68.     c_nation,
  69.     s_nation,
  70.     d_year,
  71.     SUM(lo_revenue) AS REVENUE
  72. FROM customer, lineorder, supplier, dates
  73. WHERE
  74.     lo_custkey = c_custkey
  75.   AND lo_suppkey = s_suppkey
  76.   AND lo_orderdate = d_datekey
  77.   AND c_region = 'ASIA'
  78.   AND s_region = 'ASIA'
  79.   AND d_year >= 1992
  80.   AND d_year <= 1997
  81. GROUP BY c_nation, s_nation, d_year
  82. ORDER BY d_year ASC, REVENUE DESC;
  84. --Q3.2
  85. SELECT
  86.     c_city,
  87.     s_city,
  88.     d_year,
  89.     SUM(lo_revenue) AS REVENUE
  90. FROM customer, lineorder, supplier, dates
  91. WHERE
  92.     lo_custkey = c_custkey
  93.   AND lo_suppkey = s_suppkey
  94.   AND lo_orderdate = d_datekey
  95.   AND c_nation = 'UNITED STATES'
  96.   AND s_nation = 'UNITED STATES'
  97.   AND d_year >= 1992
  98.   AND d_year <= 1997
  99. GROUP BY c_city, s_city, d_year
  100. ORDER BY d_year ASC, REVENUE DESC;
  102. --Q3.3
  103. SELECT
  104.     c_city,
  105.     s_city,
  106.     d_year,
  107.     SUM(lo_revenue) AS REVENUE
  108. FROM customer, lineorder, supplier, dates
  109. WHERE
  110.     lo_custkey = c_custkey
  111.   AND lo_suppkey = s_suppkey
  112.   AND lo_orderdate = d_datekey
  113.   AND (
  114.             c_city = 'UNITED KI1'
  115.         OR c_city = 'UNITED KI5'
  116.     )
  117.   AND (
  118.             s_city = 'UNITED KI1'
  119.         OR s_city = 'UNITED KI5'
  120.     )
  121.   AND d_year >= 1992
  122.   AND d_year <= 1997
  123. GROUP BY c_city, s_city, d_year
  124. ORDER BY d_year ASC, REVENUE DESC;
  126. --Q3.4
  127. SELECT
  128.     c_city,
  129.     s_city,
  130.     d_year,
  131.     SUM(lo_revenue) AS REVENUE
  132. FROM customer, lineorder, supplier, dates
  133. WHERE
  134.     lo_custkey = c_custkey
  135.   AND lo_suppkey = s_suppkey
  136.   AND lo_orderdate = d_datekey
  137.   AND (
  138.             c_city = 'UNITED KI1'
  139.         OR c_city = 'UNITED KI5'
  140.     )
  141.   AND (
  142.             s_city = 'UNITED KI1'
  143.         OR s_city = 'UNITED KI5'
  144.     )
  145.   AND d_yearmonth = 'Dec1997'
  146. GROUP BY c_city, s_city, d_year
  147. ORDER BY d_year ASC, REVENUE DESC;
  149. --Q4.1
  150. SELECT
  151.     d_year,
  152.     c_nation,
  153.     SUM(lo_revenue - lo_supplycost) AS PROFIT
  154. FROM dates, customer, supplier, part, lineorder
  155. WHERE
  156.     lo_custkey = c_custkey
  157.   AND lo_suppkey = s_suppkey
  158.   AND lo_partkey = p_partkey
  159.   AND lo_orderdate = d_datekey
  160.   AND c_region = 'AMERICA'
  161.   AND s_region = 'AMERICA'
  162.   AND (
  163.             p_mfgr = 'MFGR#1'
  164.         OR p_mfgr = 'MFGR#2'
  165.     )
  166. GROUP BY d_year, c_nation
  167. ORDER BY d_year, c_nation;
  169. --Q4.2
  170. SELECT
  171.     d_year,
  172.     s_nation,
  173.     p_category,
  174.     SUM(lo_revenue - lo_supplycost) AS PROFIT
  175. FROM dates, customer, supplier, part, lineorder
  176. WHERE
  177.     lo_custkey = c_custkey
  178.   AND lo_suppkey = s_suppkey
  179.   AND lo_partkey = p_partkey
  180.   AND lo_orderdate = d_datekey
  181.   AND c_region = 'AMERICA'
  182.   AND s_region = 'AMERICA'
  183.   AND (
  184.             d_year = 1997
  185.         OR d_year = 1998
  186.     )
  187.   AND (
  188.             p_mfgr = 'MFGR#1'
  189.         OR p_mfgr = 'MFGR#2'
  190.     )
  191. GROUP BY d_year, s_nation, p_category
  192. ORDER BY d_year, s_nation, p_category;
  194. --Q4.3
  195. SELECT
  196.     d_year,
  197.     s_city,
  198.     p_brand,
  199.     SUM(lo_revenue - lo_supplycost) AS PROFIT
  200. FROM dates, customer, supplier, part, lineorder
  201. WHERE
  202.     lo_custkey = c_custkey
  203.   AND lo_suppkey = s_suppkey
  204.   AND lo_partkey = p_partkey
  205.   AND lo_orderdate = d_datekey
  206.   AND s_nation = 'UNITED STATES'
  207.   AND (
  208.             d_year = 1997
  209.         OR d_year = 1998
  210.     )
  211.   AND p_category = 'MFGR#14'
  212. GROUP BY d_year, s_city, p_brand
  213. ORDER BY d_year, s_city, p_brand;
复制代码
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

嚴華

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表