sql - 如何在postgresql中对重叠数据进行汇总？

Question

我有如下数据:

t_id s_id   date_1      date_2      T_count            expected Result
N1  1-1I    2012-12-11  2013-01-22  0.21327014218009478622 4.7
N2  1-1I    2011-08-03  2011-11-10  3.8461538461538462     4.7
N3  1-1I    2013-12-05  2013-12-20  1.6935483870967742     4.7
N4  1-1I    2014-12-08  2015-06-25  4.7727272727272727     4.7
N5  1-1I    2017-03-08  (null)      (null)                 4.7
N6  1-I6    2013-04-11  2013-10-03  0.61538461538461538462 0.97
N7  1-I6    2011-09-27  2013-06-20  0.37325038880248833651 0.97

如果相同 s_id 的 date1 和 date2 与不同 t_id 的相同 s_id 重叠，我想计算 t_count 值的总和，否则返回相同 s_id 的最大值。

现在我正在使用的 postgres 代码正在计算值的总和而不考虑重叠，请你提出建议。

对于给定的 s_id，可能存在某些数据重叠而某些数据不重叠的情况，因此对于重叠的年份，我们需要对其进行一些处理并找到所有情况的最大值，例如个体，重叠并给出一个答案是最大的。

select t1.s_id,sum(t1.t_count) 
              from abc t1
              JOIN abc t2
              ON daterange(t1.date_1, t1.date_2, '[]')
                             && daterange(t1.date_1, t1.date_2, '[]')
                                      and t1.s_id = t2.s_id
              GROUP BY t1.s_id

Answer 1

如果某个 s_id 的某些间隔重叠而另一些不重叠，则不清楚逻辑应该如何工作。

如果您知道在您的数据中所有区间重叠或不重叠，那么就很容易确定它。

计算每个间隔的持续时间并总结所有这些持续时间。将它与从第一个日期到最后一个日期的持续时间进行比较。

我假设 date_2 中的 NULL 表示 future 某个非常大的日期。

WITH
CTE
AS
(
    SELECT
        s_id
        ,SUM(COALESCE(date_2, '9999-12-31') - date_1) AS TotalDuration
        ,MIN(date_1) AS FirstDate
        ,MAX(COALESCE(date_2, '9999-12-31')) AS LastDate
        ,SUM(T_count) AS SumCount
        ,MAX(T_count) AS MaxCount
    FROM YourTable
    GROUP BY s_id
)
SELECT
    s_id
    ,CASE WHEN TotalDuration > (LastDate - FirstDate)
    THEN -- intervals overlap
        SumCount
    ELSE -- none of the intervals overlap
        MaxCount
    END AS Result
FROM CTE
;