10亿数据秒级查询，ClickHouse太快了！( 二 ) _扫描

<= 'MFGR#2228')) AND (S_REGION = 'ASIA')GROUP BY year,P_BRANDORDER BY year ASC,P_BRAND ASC┌─sum(LO_REVENUE)─┬─year─┬─P_BRAND───┐│66450349438 │ 1992 │ MFGR#2221 ││65423264312 │ 1992 │ MFGR#2222 ││........... │ .... │ ......... ││39907545239 │ 1998 │ MFGR#2227 ││40654201840 │ 1998 │ MFGR#2228 │└─────────────────┴──────┴───────────┘56 rows in set. Elapsed: 1.242 sec. Processed 600.04 million rows, 5.60 GB (482.97 million rows/s., 4.51 GB/s.)
扫描行数：600,040,000，大约 6 亿。
耗时（秒）：1.242 。
查询列数：3 。
结果行数：56 。
⑤Query 3.1
SELECT C_NATION,S_NATION,toYear(LO_ORDERDATE) AS year,sum(LO_REVENUE) AS revenueFROM lineorder_flatWHERE (C_REGION = 'ASIA') AND (S_REGION = 'ASIA') AND (year >= 1992) AND (year <= 1997)GROUP BY C_NATION,S_NATION,yearORDER BY year ASC,revenue DESC┌─C_NATION──┬─S_NATION──┬─year─┬──────revenue─┐│ INDIA│ INDIA│ 1992 │ 537778456208 ││ INDONESIA │ INDIA│ 1992 │ 536684093041 ││ .....│ .......│ .... │ ............ ││ CHINA│ CHINA│ 1997 │ 525562838002 ││ JAPAN│ VIETNAM│ 1997 │ 525495763677 │└───────────┴───────────┴──────┴──────────────┘150 rows in set. Elapsed: 3.533 sec. Processed 546.67 million rows, 5.48 GB (154.72 million rows/s., 1.55 GB/s.)
扫描行数：546,670,000，大约 5 亿 4 千多万。
耗时（秒）：3.533 。
查询列数：4 。
结果行数：150 。
⑥Query 3.2
SELECT C_CITY,S_CITY,toYear(LO_ORDERDATE) AS year,sum(LO_REVENUE) AS revenueFROM lineorder_flatWHERE (C_NATION = 'UNITED STATES') AND (S_NATION = 'UNITED STATES') AND (year >= 1992) AND (year <= 1997)GROUP BY C_CITY,S_CITY,yearORDER BY year ASC,revenue DESC┌─C_CITY─────┬─S_CITY─────┬─year─┬────revenue─┐│ UNITED ST6 │ UNITED ST6 │ 1992 │ 5694246807 ││ UNITED ST0 │ UNITED ST0 │ 1992 │ 5676049026 ││ .......... │ .......... │ .... │ .......... ││ UNITED ST9 │ UNITED ST9 │ 1997 │ 4836163349 ││ UNITED ST9 │ UNITED ST5 │ 1997 │ 4769919410 │└────────────┴────────────┴──────┴────────────┘600 rows in set. Elapsed: 1.000 sec. Processed 546.67 million rows, 5.56 GB (546.59 million rows/s., 5.56 GB/s.)
扫描行数：546,670,000，大约 5 亿 4 千多万。
耗时（秒）：1.00 。
查询列数：4 。
结果行数：600 。
⑦Query 4.1

文章插图

SELECT toYear(LO_ORDERDATE) AS year,C_NATION,sum(LO_REVENUE - LO_SUPPLYCOST) AS profitFROM lineorder_flatWHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2'))GROUP BY year,C_NATIONORDER BY year ASC,C_NATION ASC┌─year─┬─C_NATION──────┬────────profit─┐│ 1992 │ ARGENTINA│ 1041983042066 ││ 1992 │ BRAZIL│ 1031193572794 ││ .... │ ......│............ ││ 1998 │ PERU│603980044827 ││ 1998 │ UNITED STATES │605069471323 │└──────┴───────────────┴───────────────┘35 rows in set. Elapsed: 5.066 sec. Processed 600.04 million rows, 8.41 GB (118.43 million rows/s., 1.66 GB/s.)

扫描行数：600,040,000，大约 6 亿。
耗时（秒）：5.066 。
查询列数：4 。