-- 1. 日期和用户关联SELECT/*+ mapjoin(b) */-- MC 运行环境要求, 没有使用 MapJoin 不能使用笛卡尔积, join 条件不能使用范围类型
a.dt_date, b.user_id, b.start_date, b.end_date
FROM t_dt a
LEFTJOIN t_emp_info b
ON a.dt_date BETWEEN b.start_date AND NVL(b.end_date,'2023-11-30');
dt_date
user_id
start_date
end_date
2023-09-10
1001
2023-08-14
2023-09-23
2023-09-20
1001
2023-08-14
2023-09-23
2023-09-01
1001
2023-08-14
2023-09-23
2023-09-11
1001
2023-08-14
2023-09-23
2023-09-21
1001
2023-08-14
2023-09-23
2023-09-02
1001
2023-08-14
2023-09-23
2023-09-12
1001
2023-08-14
2023-09-23
2023-09-22
1001
2023-08-14
2023-09-23
2023-09-03
1001
2023-08-14
2023-09-23
2023-09-13
1001
2023-08-14
2023-09-23
2023-09-23
1001
2023-08-14
2023-09-23
2023-09-04
1001
2023-08-14
2023-09-23
…
…
…
…
-- 2. 统计每月实际在职人数SELECT
DATE_FORMAT(dt_date,'yyyy-MM')AS mt -- 月份,COUNT(user_id)AS user_day_ct -- 在职人天数(本月所有在职人, 总共在职多少天),COUNT(DISTINCT dt_date)AS mt_ct -- 本月天数,ROUND(COUNT(user_id)/COUNT(DISTINCT dt_date),3)AS user_ct
FROM(SELECT/*+ mapjoin(b) */-- MC 运行环境要求, 没有使用 MapJoin 不能使用笛卡尔积, join 条件不能使用范围类型
a.dt_date, b.user_id, b.start_date, b.end_date
FROM t_dt a
LEFTJOIN t_emp_info b
ON a.dt_date BETWEEN b.start_date AND NVL(b.end_date,'2023-11-30')) c
GROUPBY DATE_FORMAT(dt_date,'yyyy-MM');-- 2. 方法二: 笛卡尔积SELECT
DATE_FORMAT(dt_date,'yyyy-MM')AS mt -- 月份,COUNT(user_id)AS user_day_ct -- 在职人天数(本月所有在职人, 总共在职多少天),COUNT(DISTINCT dt_date)AS mt_ct -- 本月天数,ROUND(COUNT(user_id)/COUNT(DISTINCT dt_date),3)AS user_ct
FROM(SELECT-- join 时使用 笛卡尔积, 下游进行过滤/*+ mapjoin(b) */-- MC 运行环境要求, 没有使用 MapJoin 不能使用笛卡尔积, join 条件不能使用范围类型
a.dt_date, b.user_id, b.start_date, b.end_date
FROM t_dt a
LEFTJOIN t_emp_info b
) c
WHERE dt_date BETWEEN start_date AND NVL(end_date,'2023-11-30')GROUPBY DATE_FORMAT(dt_date,'yyyy-MM');