Clickhouse 生成日历表

运行环境:
SELECT version()

Query id: 90d74a1e-3ce1-42b4-8b66-bd09802310c4

┌─version()─┐
│ 20.12.3.3 │
└───────────┘

1 rows in set. Elapsed: 0.002 sec.

 

clickhouse的 date和datetime的时间范围目前只支持  [1970-01-01 00:00:00, 2105-12-31 23:59:59].

计算可以容纳的最大天数:
select dateDiff('day',cast('1970-01-01' as timestamp),cast('2105-12-31' as timestamp)) gap_day;

┌─gap_day─┐
│   49672 │
└─────────┘

1 rows in set. Elapsed: 0.008 sec.

SELECT count(1)
FROM
(
    SELECT (((n1.i + (n2.i * 10)) + (n3.i * 100)) + (n4.i * 1000)) + (n5.i * 10000)
    FROM num AS n1
    CROSS JOIN num AS n2
    CROSS JOIN num AS n3
    CROSS JOIN num AS n4
    CROSS JOIN num AS n5
    ORDER BY 1 ASC
) AS t

Query id: 8e6eba23-d993-496a-8167-23fbe71e6d22

┌─count(1)─┐
│   100000 │
└──────────┘

1 rows in set. Elapsed: 0.011 sec.



创建表calender ,一个字段用于存储日期即可。
create database calender;
use calender;

create table num(i int) ENGINE = MergeTree() order by i;

 insert into num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);


create table calender(date_dt timestamp) engine=MergeTree() order by date_dt;

INSERT INTO calender (date_dt) SELECT addDays(CAST('2016-01-01 00:00:00', 'timestamp'), t.id) AS date_dt
FROM
(
    SELECT (((n1.i + (n2.i * 10)) + (n3.i * 100)) + (n4.i * 1000)) + (n5.i * 10000) AS id
    FROM num AS n1
    CROSS JOIN num AS n2
    CROSS JOIN num AS n3
    CROSS JOIN num AS n4
    CROSS JOIN num AS n5
    ORDER BY 1 ASC
) AS t
WHERE t.id <= 30000

select min(date_dt),max(date_dt) ,count(date_dt) from calender;

┌────────min(date_dt)─┬────────max(date_dt)─┬─count(date_dt)─┐
│ 2016-01-01 00:00:00 │ 2098-02-19 00:00:00 │          30001 │
└─────────────────────┴─────────────────────┴────────────────┘

1 rows in set. Elapsed: 0.008 sec. Processed 30.00 thousand rows, 120.00 KB (3.98 million rows/s., 15.92 MB/s.)


create table year_biweek_startday( year String ,startday timestamp) engine=MergeTree() order by year;

INSERT INTO year_biweek_startday SELECT
    year,
    startday
FROM
(
    SELECT
        '2016' AS year,
        CAST('2016-01-04 00:00:00', 'TIMESTAMP') AS startday
    UNION ALL
    SELECT
        '2017',
        CAST('2017-01-02 00:00:00', 'TIMESTAMP')
    UNION ALL
    SELECT
        '2018',
        CAST('2018-01-01 00:00:00', 'TIMESTAMP')
    UNION ALL
    SELECT
        '2019',
        CAST('2019-01-07 00:00:00', 'TIMESTAMP')
    UNION ALL
    SELECT
        '2020',
        CAST('2020-01-06 00:00:00', 'TIMESTAMP')
    UNION ALL
    SELECT
        '2021',
        CAST('2021-01-04 00:00:00', 'TIMESTAMP')
    UNION ALL
    SELECT
        '2022',
        CAST('2022-01-03 00:00:00', 'TIMESTAMP')
) AS t

 select * from year_biweek_startday;

SELECT *
FROM year_biweek_startday

Query id: 4a95335d-a8af-45f2-925a-0883d4b57eb6

┌─year─┬────────────startday─┐
│ 2016 │ 2016-01-04 00:00:00 │
│ 2017 │ 2017-01-02 00:00:00 │
│ 2018 │ 2018-01-01 00:00:00 │
│ 2019 │ 2019-01-07 00:00:00 │
│ 2020 │ 2020-01-06 00:00:00 │
│ 2021 │ 2021-01-04 00:00:00 │
│ 2022 │ 2022-01-03 00:00:00 │
└──────┴─────────────────────┘

7 rows in set. Elapsed: 0.004 sec.



create table calender_details(
 date_dt              timestamp comment '日期 2016-01-01 00:00:00.0 timestamp'
,peroid_date          String COMMENT '日期,20201104'
,peroid_month         String COMMENT '月份,202009'
,period_quarter       String comment '季度,202001'
,year_day             int comment '自然年的第几天,范围1-366'
,year_week            int comment '自然年的第几周,范围1-53'
,year_month           int comment '自然年的第几月,范围1-12'
,year_quarter         int comment '自然年的第几季,范围1-4'
,year                 int comment '自然年,范围0001-9999'
,quarter_day          int comment '一个季度的第几天,范围1-93'
,quarter_week         int comment '一个季度的第几周,范围1-13'
,quarter_month        int comment '一个季度的第几月,范围1-3'
,month_day            int comment '一个月的第几天,范围1-31'
,month_week           int comment '一个月的第几周,范围1-5'
,month_name_en        String comment '自然月的英文全称 January--December'
,month_name_en_abbr   String comment '自然月的英文简写 Jan--Dec'
,week_day             int comment '周几 1--7'
,week_name_en         String comment '周几的英文全称 Monday--Sunday '
,week_name_en_abbr    String comment '周几的英文简写 Mon--Sun'
,biweek               int comment '自然年的第几个双周 1-26'
,biweek_startday      timestamp comment '双周的开始日期'
,biweek_endday        timestamp comment '双周的结束日期'
) engine=MergeTree()
partition by year
order by peroid_date;




 查看双周数据的测试:

select 
  c.date_dt,s.year,s.startday,dateDiff('day',s.startday,c.date_dt) diff_day, dateDiff('day',s.startday,c.date_dt)/7 bi_day, floor(datediff('day',s.startday,c.date_dt)/14)+1 biweek
  ,addDays(s.startday,cast(floor(dateDiff('day',s.startday,c.date_dt)/14) as int)*14) biweek_startday
  ,addDays(s.startday,cast(floor(dateDiff('day',s.startday,c.date_dt)/14) as int)*14+13) biweek_endtday
from  calender c inner join  year_biweek_startday s on toYear(c.date_dt)=cast(s.year as UInt16)
where toYear(c.date_dt)=2020
order by 1

 最终生成日历的脚本:

insert into  calender_details(
date_dt
,peroid_date       
,peroid_month      
,period_quarter    
,year_day          
,year_week         
,year_month        
,year_quarter
,year
,quarter_day       
,quarter_week      
,quarter_month     
,month_day         
,month_week        
,month_name_en     
,month_name_en_abbr
,week_day          
,week_name_en      
,week_name_en_abbr 
,biweek            
,biweek_startday   
,biweek_endday 
)
select 
  c.date_dt
 ,toYYYYMMDD(date_dt) period_date
 ,toYYYYMM(date_dt) period_month
 ,concat(cast(toYear(date_dt ) as String),'0',cast(toQuarter(date_dt) as String )) period_quarter
 ,toDayOfYear(date_dt) year_day
 ,toWeek(date_dt)  year_week
 ,toMonth(date_dt) year_month 
 ,toQuarter(date_dt)  year_quarter
 ,toYear(date_dt)      year
 ,dateDiff('day',toStartOfQuarter(date_dt) ,date_dt) +1 quarter_day
 ,dateDiff('week',toStartOfQuarter(date_dt),date_dt )+1 quarter_week
 ,dateDiff('month',toStartOfQuarter(date_dt),date_dt )+1 quarter_month
 ,toDayOfYear(date_dt)  month_day
 ,dateDiff('week',toStartOfMonth(date_dt),date_dt)+1  month_week
 ,case      when toMonth(date_dt)= 1  then  'January' 
              when toMonth(date_dt)= 2 then  'February' 
              when toMonth(date_dt)= 3  then  'March'     
              when toMonth(date_dt)= 4  then  'April'    
              when toMonth(date_dt)= 5  then  'May'     
              when toMonth(date_dt)=  6 then 'June'   
              when toMonth(date_dt)=  7 then 'July'    
              when toMonth(date_dt)=  8 then  'August'   
              when toMonth(date_dt)=  9 then  'September' 
              when toMonth(date_dt)= 10 then  'October'   
              when toMonth(date_dt)= 11 then 'November'  
              when toMonth(date_dt)=12 then  'December' 
         end month_name_en
,case      when toMonth(date_dt)= 1 then 'Jan'
              when toMonth(date_dt)=2  then 'Feb'
              when toMonth(date_dt)=3    then 'Mar'
              when toMonth(date_dt)=4    then 'Apr'
              when toMonth(date_dt)=5    then 'May'
              when toMonth(date_dt)=6    then 'Jun'
              when toMonth(date_dt)=7    then 'Jul'
              when toMonth(date_dt)=8    then 'Aug'
              when toMonth(date_dt)=9    then 'Sep'
              when toMonth(date_dt)=10  then 'Oct'
              when toMonth(date_dt)=11 then 'Nov'
              when toMonth(date_dt)=12 then 'Dec'
         end month_name_en_abbr
,toDayOfWeek(date_dt)  week_day
,case when toDayOfWeek(date_dt)=1  then 'Monday' 
              when toDayOfWeek(date_dt)=2 then 'Tuesday'  
              when toDayOfWeek(date_dt)=3 then 'Wednesday'  
              when toDayOfWeek(date_dt)=4 then 'Thursday' 
              when toDayOfWeek(date_dt)=5 then 'Friday' 
              when toDayOfWeek(date_dt)=6 then 'Saturday' 
              when toDayOfWeek(date_dt)=7 then 'Sunday' 
        end   week_name_en

 ,case    when toDayOfWeek(date_dt)= 1 then 'Mon'
              when toDayOfWeek(date_dt)=2 then 'Tue'
              when toDayOfWeek(date_dt)=3 then 'Wed'
              when toDayOfWeek(date_dt)=4 then 'Thu'
              when toDayOfWeek(date_dt)=5 then 'Fri'
              when toDayOfWeek(date_dt)=6 then 'Sat'
              when toDayOfWeek(date_dt)=7 then 'Sun'
        end   week_name_en_abbr 
  , floor(datediff('day',s.startday,c.date_dt)/14)+1 biweek
  ,addDays(s.startday,cast(floor(dateDiff('day',s.startday,c.date_dt)/14) as int)*14) biweek_startday
  ,addDays(s.startday,cast(floor(dateDiff('day',s.startday,c.date_dt)/14) as int)*14+13) biweek_endtday
from  calender c inner join  year_biweek_startday s on toYear(c.date_dt)=cast(s.year as UInt16)
where toYear(c.date_dt)  between 2016 and 2022
order by 1

查询验证:

 select *  from calender_details where peroid_date ='20201216'\G

SELECT *
FROM calender_details
WHERE peroid_date = '20201216'

Query id: 4f687f13-9c06-4f5f-9256-97cfdcc3d2f5

Row 1:
──────
date_dt:            2020-12-16 00:00:00
peroid_date:        20201216
peroid_month:       202012
period_quarter:     202004
year_day:           351
year_week:          50
year_month:         12
year_quarter:       4
year:               2020
quarter_day:        77
quarter_week:       12
quarter_month:      3
month_day:          351
month_week:         3
month_name_en:      December
month_name_en_abbr: Dec
week_day:           3
week_name_en:       Wednesday
week_name_en_abbr:  Wed
biweek:             25
biweek_startday:    2020-12-07 00:00:00
biweek_endday:      2020-12-20 00:00:00

1 rows in set. Elapsed: 0.005 sec.

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 技术工厂 设计师:CSDN官方博客 返回首页