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