如何用generateseries连接两个表

Question

我有两个表用户和考勤。我想在特定的时间间隔内为所有用户获取出勤率，即使用户在某些日期(如公共假日(没有出勤率。。。

我使用generate_steries来获取所有日期，但在加入和分组时，我只获得没有出席的日期的单个值。

User table:
id | name | phone
1  | arun | 123456
2  | jack | 098765 

Attendance table:
id | user_id | ckeck_in            | check_out
11 | 2       | 2021-12-30 07:30:00 | 2021-12-30 16:00:00
21 | 1       | 2021-12-28 09:18:00 | 2021-12-28 17:45:00

因此，如果我需要获得2021年12月的所有用户出勤率，我希望它像这个

final_result:
user_id | name | date       | check_in            | check_out
1       | arun | 2021-12-01 | null                | null
2       | jack | 2021-12-01 | null                | null
1       | arun | 2021-12-02 | null                | null
2       | jack | 2021-12-02 | null                | null
...
1       | arun | 2021-12-28 | 2021-12-28 09:18:00 | 2021-12-28 17:45:00
2       | jack | 2021-12-28 | null                | null
...
1       | arun | 2021-12-30 | null                | null
2       | jack | 2021-12-30 | 2021-12-30 07:30:00 | 2021-12-30 16:00:00

PS：一个用户一天可以有多个check_in和check_out。

提前感谢！

Answer 1

您可以将cross join与left join:一起使用

select t.*, a.check_in, a.check_out from 
(select u.*, v from users u 
cross join generate_series('2021-12-01', '2021-12-31', interval '1 day') v) t
left join attendance a on date(a.check_in)= date(t.v) and t.id = a.user_id

请参阅演示。

Answer 2

首先生成所需月份的序列。然后您可以使用left join。

select distinct user_id, name, md.date,  case when date::date=check_in::date then
check_in else null end as check_in, case when date::date=check_out::date then 
check_out else null end as check_out from month_data md, attendance_table a 
left join user_table u on u.id=a.user_id  order by date, user_id;

Fiddle Here