ClickHouse入门实例-样例数据（ontime-航班飞行数据）

水深无声 2022-12-07 01:27 208阅读 0赞

参考官方文档[https://clickhouse.tech/docs/en/getting-started/example-datasets/ontime/][https_clickhouse.tech_docs_en_getting-started_example-datasets_ontime]

### 1、下载数据 ###

https://yadi.sk/d/pOZxpa42sDdgm  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center]

### 2、加压缩 ###

[root@elastic1 data]# ll
    total 15439336
    -rw-r--r-- 1 root root 3532492212 Sep 14 15:38 ontime.csv.xz
    ...
    [root@elastic1 data]# xz -dk ontime.csv.xz 
    [root@elastic1 data]#

[root@elastic1 data]# ls -lh /tpdata/data/
    total 75G
    -rw-r--r-- 1 root root  62G Sep 14 15:38 ontime.csv
    -rw-r--r-- 1 root root 3.3G Sep 14 15:38 ontime.csv.xz
    ....
    
    [root@elastic1 data]#

### 3、新建表 ###

CREATE TABLE `ontime` (
      `Year` UInt16,
      `Quarter` UInt8,
      `Month` UInt8,
      `DayofMonth` UInt8,
      `DayOfWeek` UInt8,
      `FlightDate` Date,
      `UniqueCarrier` FixedString(7),
      `AirlineID` Int32,
      `Carrier` FixedString(2),
      `TailNum` String,
      `FlightNum` String,
      `OriginAirportID` Int32,
      `OriginAirportSeqID` Int32,
      `OriginCityMarketID` Int32,
      `Origin` FixedString(5),
      `OriginCityName` String,
      `OriginState` FixedString(2),
      `OriginStateFips` String,
      `OriginStateName` String,
      `OriginWac` Int32,
      `DestAirportID` Int32,
      `DestAirportSeqID` Int32,
      `DestCityMarketID` Int32,
      `Dest` FixedString(5),
      `DestCityName` String,
      `DestState` FixedString(2),
      `DestStateFips` String,
      `DestStateName` String,
      `DestWac` Int32,
      `CRSDepTime` Int32,
      `DepTime` Int32,
      `DepDelay` Int32,
      `DepDelayMinutes` Int32,
      `DepDel15` Int32,
      `DepartureDelayGroups` String,
      `DepTimeBlk` String,
      `TaxiOut` Int32,
      `WheelsOff` Int32,
      `WheelsOn` Int32,
      `TaxiIn` Int32,
      `CRSArrTime` Int32,
      `ArrTime` Int32,
      `ArrDelay` Int32,
      `ArrDelayMinutes` Int32,
      `ArrDel15` Int32,
      `ArrivalDelayGroups` Int32,
      `ArrTimeBlk` String,
      `Cancelled` UInt8,
      `CancellationCode` FixedString(1),
      `Diverted` UInt8,
      `CRSElapsedTime` Int32,
      `ActualElapsedTime` Int32,
      `AirTime` Int32,
      `Flights` Int32,
      `Distance` Int32,
      `DistanceGroup` UInt8,
      `CarrierDelay` Int32,
      `WeatherDelay` Int32,
      `NASDelay` Int32,
      `SecurityDelay` Int32,
      `LateAircraftDelay` Int32,
      `FirstDepTime` String,
      `TotalAddGTime` String,
      `LongestAddGTime` String,
      `DivAirportLandings` String,
      `DivReachedDest` String,
      `DivActualElapsedTime` String,
      `DivArrDelay` String,
      `DivDistance` String,
      `Div1Airport` String,
      `Div1AirportID` Int32,
      `Div1AirportSeqID` Int32,
      `Div1WheelsOn` String,
      `Div1TotalGTime` String,
      `Div1LongestGTime` String,
      `Div1WheelsOff` String,
      `Div1TailNum` String,
      `Div2Airport` String,
      `Div2AirportID` Int32,
      `Div2AirportSeqID` Int32,
      `Div2WheelsOn` String,
      `Div2TotalGTime` String,
      `Div2LongestGTime` String,
      `Div2WheelsOff` String,
      `Div2TailNum` String,
      `Div3Airport` String,
      `Div3AirportID` Int32,
      `Div3AirportSeqID` Int32,
      `Div3WheelsOn` String,
      `Div3TotalGTime` String,
      `Div3LongestGTime` String,
      `Div3WheelsOff` String,
      `Div3TailNum` String,
      `Div4Airport` String,
      `Div4AirportID` Int32,
      `Div4AirportSeqID` Int32,
      `Div4WheelsOn` String,
      `Div4TotalGTime` String,
      `Div4LongestGTime` String,
      `Div4WheelsOff` String,
      `Div4TailNum` String,
      `Div5Airport` String,
      `Div5AirportID` Int32,
      `Div5AirportSeqID` Int32,
      `Div5WheelsOn` String,
      `Div5TotalGTime` String,
      `Div5LongestGTime` String,
      `Div5WheelsOff` String,
      `Div5TailNum` String
    ) ENGINE = MergeTree
    PARTITION BY Year
    ORDER BY (Carrier, FlightDate)
    SETTINGS index_granularity = 8192;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 1]

### 4、导入数据 ###

[root@client tpdata]# cat ontime.csv | clickhouse-client --password --query "INSERT INTO ontime FORMAT CSV" --max_insert_block_size=100000
    Password for user (default): 
    [root@client tpdata]#

报错超时

[root@client tpdata]# clickhouse-client --password --query "OPTIMIZE TABLE ontime FINAL"
    Password for user (default): 
    Timeout exceeded while receiving data from server. Waited for 300 seconds, timeout is 300 seconds.
    [root@client tpdata]#

### 5、查询实例 ###

（1）查询总记录数

[root@client tpdata]# clickhouse-client --password --query "select count(*) from ontime"
    Password for user (default): 
    166628027
    [root@client tpdata]#

（2）没有平均班次

SELECT avg(c1)
    FROM
    (
        SELECT Year, Month, count(*) AS c1
        FROM ontime
        GROUP BY Year, Month
    );

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 2]  
（3）查询从2000年到2008年每天的航班数

SELECT DayOfWeek, count(*) AS c
    FROM ontime
    WHERE Year>=2000 AND Year<=2008
    GROUP BY DayOfWeek
    ORDER BY c DESC;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 3]

（4）查询从2000年到2008年每周延误超过10分钟的航班数。

SELECT DayOfWeek, count(*) AS c
    FROM ontime
    WHERE DepDelay>10 AND Year>=2000 AND Year<=2008
    GROUP BY DayOfWeek
    ORDER BY c DESC;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 4]  
（5）查询2000年到2008年每个机场延误超过10分钟以上的次数

SELECT Origin, count(*) AS c
    FROM ontime
    WHERE DepDelay>10 AND Year>=2000 AND Year<=2008
    GROUP BY Origin
    ORDER BY c DESC
    LIMIT 10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 5]  
（6）查询2007年各航空公司延误超过10分钟以上的次数

SELECT Carrier, count(*)
    FROM ontime
    WHERE DepDelay>10 AND Year=2007
    GROUP BY Carrier
    ORDER BY count(*) DESC;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 6]  
（7）查询2007年各航空公司延误超过10分钟以上的百分比

SELECT Carrier, avg(DepDelay>10)*100 AS c3
    FROM ontime
    WHERE Year=2007
    GROUP BY Carrier
    ORDER BY c3 DESC

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 7]  
（8）同上一个查询一致,只是查询范围扩大到2000年到2008年

SELECT Carrier, avg(DepDelay>10)*100 AS c3
    FROM ontime
    WHERE Year>=2000 AND Year<=2008
    GROUP BY Carrier
    ORDER BY c3 DESC;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 8]

（9）每年航班延误超过10分钟的百分比

SELECT Year, avg(DepDelay>10)*100
    FROM ontime
    GROUP BY Year
    ORDER BY Year;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 9]

（10）每年更受人们喜爱的目的地

SELECT DestCityName, uniqExact(OriginCityName) AS u
    FROM ontime
    WHERE Year >= 2000 and Year <= 2010
    GROUP BY DestCityName
    ORDER BY u DESC LIMIT 10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 10]  
（11）按年份统计

SELECT Year, count(*) AS c1
    FROM ontime
    GROUP BY Year;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 11]

（12）查询2010年最受欢迎的目的地

SELECT 
        OriginCityName, 
        DestCityName, 
        count(*) AS flights, 
        bar(flights, 0, 20000, 40)
    FROM ontime
    WHERE Year = 2010
    GROUP BY 
        OriginCityName, 
        DestCityName
    ORDER BY flights DESC
    LIMIT 20

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 12]

（13）最长飞行时间

SELECT 
        OriginCityName, 
        DestCityName, 
        count(*) AS flights, 
        avg(AirTime) AS duration
    FROM ontime
    GROUP BY 
        OriginCityName, 
        DestCityName
    ORDER BY duration DESC

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 13]

[https_clickhouse.tech_docs_en_getting-started_example-datasets_ontime]: https://clickhouse.tech/docs/en/getting-started/example-datasets/ontime/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/b67f88803f3749e1ad0d7496cb535826.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/fe959c0466254001898bdae294989c86.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/1b82cedd34b94a26b8e826674a1720a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/eb8ad4cc8d984b71bb3298dbb40e14d8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/6fe7ad3fb6854c8e80f8daa74a181f2f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221123/89d0bff8871a4f7a9ce26bb876535fa0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221123/a347344d557049f3aa3fa8994c3086e0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221123/2f9d75bcc37b4be5b19bafb15db4c0f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221123/a9279f6bc5034ba2a5ba1ba79bfb3cf2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221123/2d13251f203b483280dafa57c4353fd8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 10]: /images/20221123/a250faca1bdc42aea5ab065a9f80a57f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 11]: /images/20221123/117ec441d67742cdb989fab406f3f77f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 12]: /images/20221123/acbb6619f2114100b8471476954f5c16.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5neXVxaWFuZw_size_16_color_FFFFFF_t_70_pic_center 13]: /images/20221123/e972cfc1e8ef4df3803575e3feb33a37.png