时间戳

首页 > 解决方案 > PostgreSQL - 使用子查询生成系列

sql - PostgreSQL - 使用子查询生成系列

问题描述

使用 PostgreSQL，我需要完成以下场景。我有一个名为routine 的表，我在其中存储 start_date 和 end_date 列。我有另一个名为练习的表，我在其中存储与每个练习相关的所有数据，最后，我有一个名为routine_exercise 的表，我在其中创建了例程和练习之间的关系。每个例程可以有 7 天（一天表示一周中的哪一天，例如：1 表示星期一等）的锻炼，并且每天可以有一个或多个锻炼。例如：

运动表

练习编号 姓名
1 练习 1
2 练习 2
3 练习 3

例行表

例程 ID 姓名
1 例程 1
2 例程 2
3 例程 3

Routine_Exercise 表

练习编号 例程 ID 天
1 1 1
2 1 1
3 1 1
1 1 2
2 1 3
3 1 4

我想要做的是生成一个从 start_date 到 end_date 的系列（例如 03-25-2020 到 05-25-2020，两个月），并为每个日期分配它应该工作的天数。例如，使用Routine_Exercise 表中的数据，用户应该只锻炼天数：1、2、3、4，所以我想将该数字附加到每个日期。例如，像这样：

预期结果

日期 数字
03-25-2020 1
03-26-2020 2
03-27-2020 3
03-28-2020 4
2020 年 3 月 29 日 无效的
03-30-2020 无效的
03-31-2020 无效的
04-01-2020 1
04-02-2020 2
04-03-2020 3
04-04-2020 4
04-05-2020 无效的

关于如何实现这一点的任何建议或不同的想法？另一个不需要系列的解决方案？提前致谢！

标签： sqldatabasepostgresqldateseries

解决方案

您可以使用生成开始和结束输入日期之间的日期generate_series，然后按如下left join方式处理您的表格：routine_exercise

SELECT t.d, re.day
  FROM generate_series(timestamp '2020-03-25', timestamp '2020-05-25',
                       interval  '1 day') AS t(d)
  left join (select distinct day from Routine_Exercise re WHERE ROUTINE_ID = 1) re
    on mod(extract(day from (t.d -timestamp '2020-03-25')), 7) + 1 = re.day;

推荐阅读