mysql - 如何将开始日期和结束日期与其他开始日期和结束日期分开？

Question

我在下面写了两个查询，结果看起来与此类似:

表:company_changes

<表类="s-表"><头>user_id开始时间结束于公司编号<正文>1892020-12-122021-03-02881892021-03-022050-01-01169

表:enablement_changes

<表类="s-表"><头>user_id开始时间结束于启用<正文>1892020-12-122021-10-15已禁用1892021-10-152050-01-01启用

重要的是我知道用户何时在某个 company_id并且是 enabled或 disabled .

我想要的结果是这样的表格:

<表类="s-表"><头>user_id开始时间结束于公司编号状态<正文>1892020-12-122021-03-0288已禁用1892021-03-022021-10-15169已禁用1892021-10-152050-01-01169启用

我基本上想将这些查询的结果组合在一起。 2050-01-01 是 future 的任意日期。自 user_id没有改变status或 company_id然后它显示为 2050-01-01，因为它是用户的当前状态。

知道如何解决这个问题吗？

这是 fiddle :http://sqlfiddle.com/#!9/5c42b6

第一次在 Stackoverflow 上提问...如果我的问题格式不正确，请告诉我。

Answer 1

如果在实践中你有更复杂的数据并且可能有重叠的时间间隔，例如:

表:enablement_changes

<表类="s-表"><头>user_id开始时间结束于启用<正文>1892020-12-122021-10-15已禁用189 2020-12-20 2021-02-10 启用1892021-10-152050-01-01启用

我推荐一个更复杂的解决方案:

WITH _k AS (
    SELECT 1 AS n
    UNION ALL
    SELECT 2 AS n
), _points AS (
  SELECT user_id, CASE WHEN n = 1 THEN start_at ELSE end_at END AS date_point, n
    FROM company_changes
   CROSS JOIN _k
   UNION
  SELECT user_id, CASE WHEN n = 1 THEN start_at ELSE end_at END AS date_point, n
    FROM enablement_changes
   CROSS JOIN _k
), _drank AS (
  SELECT p.user_id, p.date_point, DENSE_RANK() OVER(PARTITION BY p.user_id ORDER BY p.date_point) AS dr
    FROM _points AS p
   GROUP BY p.user_id, p.date_point
)
SELECT d1.user_id, d1.date_point AS start_at, d2.date_point AS end_at, c.company_id, MAX(s.status) AS status -- or MIN if status disabled is stronger than enabled in the same time
  FROM _drank AS d1
  JOIN _drank AS d2 ON d1.dr = d2.dr-1 AND d1.user_id = d2.user_id
  LEFT JOIN company_changes AS c    ON d1.user_id = c.user_id AND d1.date_point < c.end_at AND c.start_at < d2.date_point 
  LEFT JOIN enablement_changes AS s ON d1.user_id = s.user_id AND d1.date_point < s.end_at AND s.start_at < d2.date_point 
 GROUP BY d1.user_id, d1.date_point, d2.date_point, c.company_id
 ORDER BY 1,2,3;

db<>fiddle demo

输出: