提问人：Simonas Petkevičius 提问时间：3/16/2023 更新时间：3/16/2023 访问量：58
是否有可能避免 CASE ...当需要两个不同的开始日期时generate_series语句中的 WHEN
Is it possible to avoid CASE ... WHEN in generate_series statement when two different beginning dates are required
问：
我有一个查询，我根据后端的查询参数用于生成不同的日期序列。
要点是，对于第一个月，我需要输入日期，而对于生成的其余月份，应该是月初，如下图所示。dategenerate_seriesdate
以下是三种方法，我如何在下面使用它并获得正确的结果。
月份序列 -> 的示例
SELECT
date::DATE AS sequence_value,
CASE WHEN date = '2022-01-16'
THEN date::DATE
ELSE date_trunc('MONTH', date)::DATE END AS date,
(DATE_TRUNC('MONTH', date) + INTERVAL '1 MONTH')::DATE AS date_end,
to_char(date, 'MONTH') AS name
FROM GENERATE_SERIES('2022-01-16', '2023-01-02', INTERVAL '1 MONTH') AS date
RESULTS:
"sequence_value" "date" "date_end" "name"
"2022-01-16" "2022-01-16" "2022-02-01" "JANUARY "
"2022-02-16" "2022-02-01" "2022-03-01" "FEBRUARY "
"2022-03-16" "2022-03-01" "2022-04-01" "MARCH "
"2022-04-16" "2022-04-01" "2022-05-01" "APRIL "
周序列 -> 的示例
SELECT
date::DATE AS sequence_value,
CASE WHEN date = '2022-01-16'
THEN date::DATE
ELSE date_trunc('WEEK', date)::DATE END AS date,
(DATE_TRUNC('WEEK', date) + INTERVAL '1 WEEK')::DATE AS date_end,
to_char(date, 'MM-DD') AS name
FROM GENERATE_SERIES('2022-01-16', '2023-01-02', INTERVAL '1 WEEK') AS date
RESULTS:
"sequence_value" "date" "date_end" "name"
"2022-01-16" "2022-01-16" "2022-01-17" "01-16"
"2022-01-23" "2022-01-17" "2022-01-24" "01-23"
"2022-01-30" "2022-01-24" "2022-01-31" "01-30"
"2022-02-06" "2022-01-31" "2022-02-07" "02-06"
日期序列 -> 的示例
SELECT
date::DATE AS sequence_value,
CASE WHEN date = '2022-01-16'
THEN date::DATE
ELSE date_trunc('DAY', date)::DATE END AS date,
(DATE_TRUNC('DAY', date) + INTERVAL '1 DAY')::DATE AS date_end,
to_char(date, 'MM-DD') AS name
FROM GENERATE_SERIES('2022-01-16', '2023-01-02', INTERVAL '1 DAY') AS date
RESULTS:
"sequence_value" "date" "date_end" "name"
"2022-01-16" "2022-01-16" "2022-01-17" "01-16"
"2022-01-17" "2022-01-17" "2022-01-18" "01-17"
"2022-01-18" "2022-01-18" "2022-01-19" "01-18"
"2022-01-19" "2022-01-19" "2022-01-20" "01-19"
有没有办法使查询更简洁、更高效？
也许，可以更换 CASE ......WHEN语句与别的东西？
截至目前，我在日序列生成中并不真正需要它，因为它每天都会生成日期，但是，对于月份序列生成，这是必要的，否则，我将获得第 16 天的月份开始日期。
答：
2赞
MatBailie
3/16/2023
#1
这些是我会去的几种方法......
SELECT
GREATEST('2022-01-16'::date, s.date ) AS date_start,
LEAST( '2023-01-01'::date, s.date + INTERVAL '1 MONTH') AS date_end,
to_char(s.date, 'MONTH') AS name
FROM
GENERATE_SERIES(
DATE_TRUNC('MONTH', '2022-01-16'::date),
'2023-01-01'::date - INTERVAL '1 DAY',
INTERVAL '1 MONTH'
)
AS s
| date_start | date_end | 名字 |
|---|---|---|
| 2022-01-16 | 2022-02-01 00:00:00 | 一月 |
| 2022-02-01 | 2022-03-01 00:00:00 | 二月 |
| 2022-03-01 | 2022-04-01 00:00:00 | 三月 |
| 2022-04-01 | 2022-05-01 00:00:00 | 四月 |
| 2022-05-01 | 2022-06-01 00:00:00 | 五月 |
| 2022-06-01 | 2022-07-01 00:00:00 | 六月 |
| 2022-07-01 | 2022-08-01 00:00:00 | 七月 |
| 2022-08-01 | 2022-09-01 00:00:00 | 八月 |
| 2022-09-01 | 2022-10-01 00:00:00 | 九月 |
| 2022-10-01 | 2022-11-01 00:00:00 | 十月 |
| 2022-11-01 | 2022-12-01 00:00:00 | NOVEMBER |
| 2022-12-01 | 2023-01-01 00:00:00 | DECEMBER |
SELECT 12
SELECT
GREATEST('2022-01-16'::date, s.date) AS date_start,
LEAST( '2023-01-01'::date, s.date) AS date_end,
to_char(m.month_start, 'MONTH') AS name
FROM
GENERATE_SERIES(
'2022-01-16'::date,
'2023-01-01'::date - INTERVAL '1 DAY',
INTERVAL '1 MONTH'
)
AS s
CROSS JOIN LATERAL
(
SELECT
DATE_TRUNC('MONTH', s.date) AS month_start,
DATE_TRUNC('MONTH', s.date) + INTERVAL '1 MONTH' AS month_end
)
AS m
| date_start | date_end | 名字 |
|---|---|---|
| 2022-01-16 | 2022-01-16 | 一月 |
| 2022-02-16 | 2022-02-16 | 二月 |
| 2022-03-16 | 2022-03-16 | 三月 |
| 2022-04-16 | 2022-04-16 | 四月 |
| 2022-05-16 | 2022-05-16 | 五月 |
| 2022-06-16 | 2022-06-16 | 六月 |
| 2022-07-16 | 2022-07-16 | 七月 |
| 2022-08-16 | 2022-08-16 | 八月 |
| 2022-09-16 | 2022-09-16 | 九月 |
| 2022-10-16 | 2022-10-16 | 十月 |
| 2022-11-16 | 2022-11-16 | NOVEMBER |
| 2022-12-16 | 2022-12-16 | DECEMBER |
SELECT 12
SELECT
GREATEST('2022-01-16'::date, s.date ) AS date_start,
LEAST( '2023-01-01'::date, s.date + INTERVAL '1 WEEK') AS date_end,
to_char(s.date, 'MM-DD') AS name
FROM
GENERATE_SERIES(
DATE_TRUNC('WEEK', '2022-01-16'::date),
'2023-01-01'::date - INTERVAL '1 DAY',
INTERVAL '1 WEEK'
)
AS s
| date_start | date_end | 名字 |
|---|---|---|
| 2022-01-16 | 2022-01-17 00:00:00 | 01-10 |
| 2022-01-17 | 2022-01-24 00:00:00 | 01-17 |
| 2022-01-24 | 2022-01-31 00:00:00 | 01-24 |
| 2022-01-31 | 2022-02-07 00:00:00 | 01-31 |
| 2022-02-07 | 2022-02-14 00:00:00 | 02-07 |
| 2022-02-14 | 2022-02-21 00:00:00 | 02-14 |
| 2022-02-21 | 2022-02-28 00:00:00 | 02-21 |
| 2022-02-28 | 2022-03-07 00:00:00 | 02-28 |
| 2022-03-07 | 2022-03-14 00:00:00 | 03-07 |
| 2022-03-14 | 2022-03-21 00:00:00 | 03-14 |
| 2022-03-21 | 2022-03-28 00:00:00 | 03-21 |
| 2022-03-28 | 2022-04-04 00:00:00 | 03-28 |
| 2022-04-04 | 2022-04-11 00:00:00 | 04-04 |
| 2022-04-11 | 2022-04-18 00:00:00 | 04-11 |
| 2022-04-18 | 2022-04-25 00:00:00 | 04-18 |
| 2022-04-25 | 2022-05-02 00:00:00 | 04-25 |
| 2022-05-02 | 2022-05-09 00:00:00 | 05-02 |
| 2022-05-09 | 2022-05-16 00:00:00 | 05-09 |
| 2022-05-16 | 2022-05-23 00:00:00 | 05-16 |
| 2022-05-23 | 2022-05-30 00:00:00 | 05-23 |
| 2022-05-30 | 2022-06-06 00:00:00 | 05-30 |
| 2022-06-06 | 2022-06-13 00:00:00 | 06-06 |
| 2022-06-13 | 2022-06-20 00:00:00 | 06-13 |
| 2022-06-20 | 2022-06-27 00:00:00 | 06-20 |
| 2022-06-27 | 2022-07-04 00:00:00 | 06-27 |
| 2022-07-04 | 2022-07-11 00:00:00 | 07-04 |
| 2022-07-11 | 2022-07-18 00:00:00 | 07-11 |
| 2022-07-18 | 2022-07-25 00:00:00 | 07-18 |
| 2022-07-25 | 2022-08-01 00:00:00 | 07-25 |
| 2022-08-01 | 2022-08-08 00:00:00 | 08-01 |
| 2022-08-08 | 2022-08-15 00:00:00 | 08-08 |
| 2022-08-15 | 2022-08-22 00:00:00 | 08-15 |
| 2022-08-22 | 2022-08-29 00:00:00 | 08-22 |
| 2022-08-29 | 2022-09-05 00:00:00 | 08-29 |
| 2022-09-05 | 2022-09-12 00:00:00 | 09-05 |
| 2022-09-12 | 2022-09-19 00:00:00 | 09-12 |
| 2022-09-19 | 2022-09-26 00:00:00 | 09-19 |
| 2022-09-26 | 2022-10-03 00:00:00 | 09-26 |
| 2022-10-03 | 2022-10-10 00:00:00 | 10-03 |
| 2022-10-10 | 2022-10-17 00:00:00 | 10-10 |
| 2022-10-17 | 2022-10-24 00:00:00 | 10-17 |
| 2022-10-24 | 2022-10-31 00:00:00 | 10-24 |
| 2022-10-31 | 2022-11-07 00:00:00 | 10-31 |
| 2022-11-07 | 2022-11-14 00:00:00 | 11-07 |
| 2022-11-14 | 2022-11-21 00:00:00 | 11-14 |
| 2022-11-21 | 2022-11-28 00:00:00 | 11-21 |
| 2022-11-28 | 2022-12-05 00:00:00 | 11-28 |
| 2022-12-05 | 2022-12-12 00:00:00 | 12-05 |
| 2022-12-12 | 2022-12-19 00:00:00 | 12-12 |
| 2022-12-19 | 2022-12-26 00:00:00 | 12-19 |
| 2022-12-26 | 2023-01-01 00:00:00 | 12-26 |
SELECT 51
评论