从日期维度表到层次结构

时间:2021-08-22 09:54:53

This is my table:

这是我的表:

idDate  timeformat  timeformatdate idYear YearName idSemester semestername idquarter quartername idmonth month idweek week idday day  

20160101    2016-01-01  01-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         53  W53         1   Friday   , 1                                      
20160102    2016-01-02  02-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         53  W53         2   Saturday , 2                                      
20160103    2016-01-03  03-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         53  W53         3   Sunday   , 3                                      
20160104    2016-01-04  04-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         1   W1          4   Monday   , 4                                      
20160105    2016-01-05  05-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         1   W1          5   Tuesday  , 5                                      
20160106    2016-01-06  06-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         1   W1          6   Wednesday, 6                                      
20160107    2016-01-07  07-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         1   W1          7   Thursday , 7                                      
20160108    2016-01-08  08-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         1   W1          8   Friday   , 8                                      
20160109    2016-01-09  09-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         1   W1          9   Saturday , 9                                      
20160110    2016-01-10  10-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         1   W1          10  Sunday   , 10                                     
20160111    2016-01-11  11-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         2   W2          11  Monday   , 11                                     
20160112    2016-01-12  12-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         2   W2          12  Tuesday  , 12                                     
20160113    2016-01-13  13-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         2   W2          13  Wednesday, 13                                     
20160114    2016-01-14  14-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         2   W2          14  Thursday , 14                                     
20160115    2016-01-15  15-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         2   W2          15  Friday   , 15                                     
20160116    2016-01-16  16-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         2   W2          16  Saturday , 16                                     
20160117    2016-01-17  17-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         2   W2          17  Sunday   , 17                                     
20160118    2016-01-18  18-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         3   W3          18  Monday   , 18                                     
20160119    2016-01-19  19-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         3   W3          19  Tuesday  , 19                                     
20160120    2016-01-20  20-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         3   W3          20  Wednesday, 20                                     
20160121    2016-01-21  21-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         3   W3          21  Thursday , 21                                     
20160122    2016-01-22  22-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         3   W3          22  Friday   , 22                                     
20160123    2016-01-23  23-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         3   W3          23  Saturday , 23                                     
20160124    2016-01-24  24-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         3   W3          24  Sunday   , 24                                     
20160125    2016-01-25  25-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         4   W4          25  Monday   , 25                                     
20160126    2016-01-26  26-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         4   W4          26  Tuesday  , 26                                     
20160127    2016-01-27  27-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         4   W4          27  Wednesday, 27                                     
20160128    2016-01-28  28-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         4   W4          28  Thursday , 28                                     
20160129    2016-01-29  29-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         4   W4          29  Friday   , 29                                     
20160130    2016-01-30  30-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         4   W4          30  Saturday , 30                                     
20160131    2016-01-31  31-Jan-16   2016    2016    1   S1  1   Q1  201601  Jan         4   W4          31  Sunday   , 31      

I'm trying to create a time_hierarchy via a SQL statement. What i want to achieve is for example:

我正在尝试通过SQL语句创建time_hierarchy。我想要实现的是例如:

Time
[2016] as Year
[Q1, 2016] as Quarter
[Jan, 2016] as Month
[Jan, 1, 2016] as Day
Etc...

I've really no idea how to achieve this or if my table do not support this kind of hierarchy.

我真的不知道如何实现这个,或者我的表不支持这种层次结构。

Could you help me?

你可以帮帮我吗?

Thanks

谢谢

1 个解决方案

#1


2  

Perhaps something like this?

也许是这样的?

SELECT
  CASE WHEN quarterName IS NULL THEN 'Year'
       WHEN month       IS NULL THEN 'Quarter'
       WHEN idDay       IS NULL THEN 'Month'
                                ELSE 'Day'
  END
    AS aggregateLevel,
  CASE WHEN quarterName IS NULL THEN YearName
       WHEN month       IS NULL THEN QuarterName || ', ' || YearName
       WHEN idDay       IS NULL THEN Month || ', ' || YearName
                                ELSE Month || ', ' || CAST(idDay AS VARCHAR(2)) || YearName
  END
    AS periodTitle,
  MIN(idDate)   AS periodFirstDate,
  MAX(idDate)   AS periodFinalDate
FROM
  yourTable
GROUP BY
  GROUPING SETS (
    (YearName),
    (YearName, quarterName),
    (YearName, quarterName, month),
    (YearName, quarterName, month, idDay)
  )
ORDER BY
  MIN(idDate),
  MAX(idDate) DESC

#1


2  

Perhaps something like this?

也许是这样的?

SELECT
  CASE WHEN quarterName IS NULL THEN 'Year'
       WHEN month       IS NULL THEN 'Quarter'
       WHEN idDay       IS NULL THEN 'Month'
                                ELSE 'Day'
  END
    AS aggregateLevel,
  CASE WHEN quarterName IS NULL THEN YearName
       WHEN month       IS NULL THEN QuarterName || ', ' || YearName
       WHEN idDay       IS NULL THEN Month || ', ' || YearName
                                ELSE Month || ', ' || CAST(idDay AS VARCHAR(2)) || YearName
  END
    AS periodTitle,
  MIN(idDate)   AS periodFirstDate,
  MAX(idDate)   AS periodFinalDate
FROM
  yourTable
GROUP BY
  GROUPING SETS (
    (YearName),
    (YearName, quarterName),
    (YearName, quarterName, month),
    (YearName, quarterName, month, idDay)
  )
ORDER BY
  MIN(idDate),
  MAX(idDate) DESC