【天穹OS】虚拟表：支持极速查询的下一代湖仓一体新范式

use test;
CREATE VIRTUAL TABLE IF NOT EXISTS `ssb_lineorder_flat` (
  `LO_ORDERDATE` INT COMMENT "",
  `LO_ORDERKEY` BIGINT COMMENT "",
  `LO_LINENUMBER` INT COMMENT "",
  `LO_CUSTKEY` INT COMMENT "",
  `LO_PARTKEY` INT COMMENT "",
  `LO_SUPPKEY` INT COMMENT "",
  `LO_ORDERPRIORITY` STRING COMMENT "",
  `LO_SHIPPRIORITY` INT COMMENT "",
  `LO_QUANTITY` INT COMMENT "",
  `LO_EXTENDEDPRICE` INT COMMENT "",
  `LO_ORDTOTALPRICE` INT COMMENT "",
  `LO_DISCOUNT` INT COMMENT "",
  `LO_REVENUE` INT COMMENT "",
  `LO_SUPPLYCOST` INT COMMENT "",
  `LO_TAX` INT COMMENT "",
  `LO_COMMITDATE` INT COMMENT "",
  `LO_SHIPMODE` STRING COMMENT "",
  `C_NAME` STRING COMMENT "",
  `C_ADDRESS` STRING COMMENT "",
  `C_CITY` STRING COMMENT "",
  `C_NATION` STRING COMMENT "",
  `C_REGION` STRING COMMENT "",
  `C_PHONE` STRING COMMENT "",
  `C_MKTSEGMENT` STRING COMMENT "",
  `S_NAME` STRING COMMENT "",
  `S_ADDRESS` STRING COMMENT "",
  `S_CITY` STRING COMMENT "",
  `S_NATION` STRING COMMENT "",
  `S_REGION` STRING COMMENT "",
  `S_PHONE` STRING COMMENT "",
  `P_NAME` STRING COMMENT "",
  `P_MFGR` STRING COMMENT "",
  `P_CATEGORY` STRING COMMENT "",
  `P_BRAND` STRING COMMENT "",
  `P_COLOR` STRING COMMENT "",
  `P_TYPE` STRING COMMENT "",
  `P_SIZE` INT COMMENT "",
  `P_CONTAINER` STRING COMMENT ""
)
PARTITION BY RANGE(`LO_ORDERDATE`)
(
        PARTITION p1 VALUES LESS THAN (19930101),
        PARTITION p2 VALUES LESS THAN (19940101),
        PARTITION p3 VALUES LESS THAN (19950101),
        PARTITION p4 VALUES LESS THAN (19960101),
        PARTITION p5 VALUES LESS THAN (19970101),
        PARTITION p6 VALUES LESS THAN (19980101),
        PARTITION p7 VALUES LESS THAN (19990101)
)
CLUSTERED BY(`LO_ORDERKEY`) SORTED BY(`LO_ORDERDATE`,`LO_ORDERKEY`) INTO 120 BUCKETS;

use test;
alter table test.ssb_lineorder_flat add partition par_name4 VALUES IN (20230804);
alter table test.ssb_lineorder_flat add partition part_20230103 VALUES LESS THAN (20230103);

alter table test.depts01 drop partition (par_name4);
alter table test.depts02 drop partition (part_20230103);

use test;
-- insert into 虚拟表 select from 天穹数据仓库表（多表/ETL场景）
INSERT OVERWRITE ssb_lineorder_flat SELECT * FROM benchmark_ssb_lineorder_flat_raw;

INSERT OVERWRITE ssb_lineorder_flat
SELECT `LO_ORDERDATE` , `LO_ORDERKEY` , `LO_LINENUMBER` , `LO_CUSTKEY` , `LO_PARTKEY` , `LO_SUPPKEY` , `LO_ORDERPRIORITY` , `LO_SHIPPRIORITY` , `LO_QUANTITY` , `LO_EXTENDEDPRICE` , `LO_ORDTOTALPRICE` , `LO_DISCOUNT` , `LO_REVENUE` , `LO_SUPPLYCOST` , `LO_TAX` , `LO_COMMITDATE` , `LO_SHIPMODE` , `C_NAME` , `C_ADDRESS` , `C_CITY` , `C_NATION` , `C_REGION` , `C_PHONE` , `C_MKTSEGMENT` , `S_NAME` , `S_ADDRESS` , `S_CITY` , `S_NATION` , `S_REGION` , `S_PHONE` , `P_NAME` , `P_MFGR` , `P_CATEGORY` , `P_BRAND` , `P_COLOR` , `P_TYPE` , `P_SIZE` , `P_CONTAINER` 
FROM (
  SELECT lo_orderkey,lo_linenumber,lo_custkey,lo_partkey,lo_suppkey,lo_orderdate,lo_orderpriority,lo_shippriority,lo_quantity,lo_extendedprice,lo_ordtotalprice,lo_discount,lo_revenue,lo_supplycost,lo_tax,lo_commitdate,lo_shipmode 
  FROM benchmark_ssb_lineorder_1000_raw 
  ) l
  INNER JOIN benchmark_ssb_customer_1000_raw c ON (c.C_CUSTKEY = l.LO_CUSTKEY)  
  INNER JOIN benchmark_ssb_supplier_1000_raw s ON (s.S_SUPPKEY = l.LO_SUPPKEY)  
  INNER JOIN benchmark_ssb_part_1000_raw p ON  (p.P_PARTKEY = l.LO_PARTKEY);

use test;
--Q1.1 
SELECT sum(lo_extendedprice * lo_discount) AS `revenue` 
FROM ssb_lineorder_flat
WHERE lo_orderdate >= '19930101' and lo_orderdate <= '19931231'
AND lo_discount BETWEEN 1 AND 3 AND lo_quantity < 25; 
 
--Q1.2 
SELECT sum(lo_extendedprice * lo_discount) AS revenue 
FROM ssb_lineorder_flat  
WHERE lo_orderdate >= '19940101' and lo_orderdate <= '19940131'
AND lo_discount BETWEEN 4 AND 6 AND lo_quantity BETWEEN 26 AND 35; 
 
--Q1.3 
SELECT sum(lo_extendedprice * lo_discount) AS revenue 
FROM ssb_lineorder_flat 
WHERE weekofyear(lo_orderdate) = 6 
AND lo_orderdate >= '19940101' and lo_orderdate <= '19941231' 
AND lo_discount BETWEEN 5 AND 7 AND lo_quantity BETWEEN 26 AND 35; 

--Q2.1 
SELECT sum(lo_revenue), year(lo_orderdate) AS year_col,  p_brand 
FROM ssb_lineorder_flat 
WHERE p_category = 'MFGR#12' AND s_region = 'AMERICA' 
GROUP BY year(lo_orderdate), p_brand 
ORDER BY year(lo_orderdate), p_brand; 

--Q2.2
SELECT 
sum(lo_revenue), year(lo_orderdate) AS year_col, p_brand 
FROM ssb_lineorder_flat 
WHERE p_brand >= 'MFGR#2221' AND p_brand <= 'MFGR#2228' AND s_region = 'ASIA' 
GROUP BY year(lo_orderdate), p_brand 
ORDER BY year(lo_orderdate), p_brand; 
  
--Q2.3
SELECT sum(lo_revenue), year(lo_orderdate) AS year_col, p_brand 
FROM ssb_lineorder_flat 
WHERE p_brand = 'MFGR#2239' AND s_region = 'EUROPE' 
GROUP BY year(lo_orderdate), p_brand 
ORDER BY year(lo_orderdate), p_brand; 
 
--Q3.1
SELECT
    c_nation,
    s_nation,
    year(lo_orderdate) AS year_col,
    sum(lo_revenue) AS revenue 
FROM ssb_lineorder_flat 
WHERE c_region = 'ASIA' AND s_region = 'ASIA' AND lo_orderdate >= '19920101'
AND lo_orderdate <= '19971231' 
GROUP BY c_nation, s_nation, year(lo_orderdate) 
ORDER BY  year(lo_orderdate) ASC, revenue DESC; 
 
--Q3.2 
SELECT c_city, s_city, year(lo_orderdate) AS year_col, sum(lo_revenue) AS revenue
FROM ssb_lineorder_flat 
WHERE c_nation = 'UNITED STATES' AND s_nation = 'UNITED STATES'
AND lo_orderdate  >= '19920101' AND lo_orderdate <= '19971231' 
GROUP BY c_city, s_city, year(lo_orderdate) 
ORDER BY year(lo_orderdate) ASC, revenue DESC; 
 
--Q3.3 
SELECT c_city, s_city, year(lo_orderdate) AS year_col, sum(lo_revenue) AS revenue 
FROM ssb_lineorder_flat 
WHERE c_city in ( 'UNITED KI1' ,'UNITED KI5') AND s_city in ('UNITED KI1', 'UNITED KI5')
AND lo_orderdate  >= '19920101' AND lo_orderdate <= '19971231' 
GROUP BY c_city, s_city, year(lo_orderdate) 
ORDER BY year(lo_orderdate) ASC, revenue DESC; 
 
--Q3.4 
SELECT c_city, s_city, year(lo_orderdate) AS year_col, sum(lo_revenue) AS revenue 
FROM ssb_lineorder_flat 
WHERE c_city in ('UNITED KI1', 'UNITED KI5') AND s_city in ('UNITED KI1', 'UNITED KI5')
AND lo_orderdate  >= '19971201' AND lo_orderdate <= '19971231' 
GROUP BY c_city, s_city, year(lo_orderdate) 
ORDER BY year(lo_orderdate) ASC, revenue DESC; 
 
--Q4.1 
SELECT year(lo_orderdate) AS year_col, c_nation, sum(lo_revenue - lo_supplycost) AS profit
FROM ssb_lineorder_flat 
WHERE c_region = 'AMERICA' AND s_region = 'AMERICA' AND p_mfgr in ('MFGR#1', 'MFGR#2') 
GROUP BY year(lo_orderdate), c_nation 
ORDER BY year(lo_orderdate) ASC, c_nation ASC; 
 
--Q4.2 
SELECT year(lo_orderdate) AS year_col, 
    s_nation, p_category, sum(lo_revenue - lo_supplycost) AS profit 
FROM ssb_lineorder_flat 
WHERE c_region = 'AMERICA' AND s_region = 'AMERICA'
AND lo_orderdate >= '19970101' and lo_orderdate <= '19981231'
AND p_mfgr in ( 'MFGR#1' , 'MFGR#2') 
GROUP BY year(lo_orderdate), s_nation, p_category 
ORDER BY year(lo_orderdate) ASC, s_nation ASC, p_category ASC; 
 
--Q4.3 
SELECT year(lo_orderdate) AS year_col, s_city, p_brand, 
    sum(lo_revenue - lo_supplycost) AS profit 
FROM ssb_lineorder_flat 
WHERE s_nation = 'UNITED STATES'
AND lo_orderdate >= '19970101' and lo_orderdate <= '19981231'
AND p_category = 'MFGR#14' 
GROUP BY year(lo_orderdate), s_city, p_brand 
ORDER BY year(lo_orderdate) ASC, s_city ASC, p_brand ASC;