如何在PostgreSQL中根据上一个周期的变化对组进行分类?

w1jd8yoj  于 2023-05-17  发布在  PostgreSQL
关注(0)|答案(2)|浏览(114)

大家好!我有一个表与一些球队和他们的评级的时期。数据看起来像这样:
| 团队|日期|额定值|
| --------------|--------------|--------------|
| 团队_1| 2022-09年|七点五|
| 团队_1| 2022-12|八|
| 团队_1| 2023年3月|八点五|
| 团队_2| 2022-09年|八|
| 团队_2| 2022-12|七|
| 团队_2| 2023年3月|八|
| 团队_3| 2022-09年|八|
| 团队_3| 2022-12|八|
| 团队_3| 2023年3月|七|
| 团队_4| 2022-09年|九|
| 团队_4| 2022-12|八|
| 团队_4| 2023年3月|七|
正如我们所看到的,球队的评分可能会比上一个时期增加或减少,也可能连续两个时期发生变化。因此,团队可分为以下组之一:
1.连续两期增加
1.较上期增加
1.与上期相比有所减少
1.连续两期下降
在这个示例中,结果应该是这样的:
| 团队|地位|
| --------------|--------------|
| 团队_1|连续两期增加|
| 团队_2|较上期增加|
| 团队_3|与上期相比有所减少|
| 团队_4|连续两期下降|
我的问题是我如何才能得到这样的结果?我怀疑应该有一些窗口函数,但我不确定如何确切地使用它。

fhg3lkii

fhg3lkii1#

是的,窗口函数,像这样:

select team, case sgn when  1 then 'increasing ' 
                      when -1 then 'decreasing '
                      else 'stable '
             end ||'for '||cnt||' period(s)' status
from (
  select team, dt, sgn, rn, 
         count(1) over (partition by team, sgn order by dt) cnt
  from ( 
    select team, dt, rating, 
           sign(rating - lag(rating) over (partition by team order by dt)) sgn, 
           row_number() over (partition by team order by dt desc) rn 
      from teams ) t1 ) t2
where rn = 1

dbfiddle demo
使用lag()比较每一行的评分,分配sign以获得值-1、0或1。然后计算每个团队的这些连续值,并取最后一行,使用row_number()找到。使用case ... when按您的需要显示值。
编辑:
在上面的查询中,我假设每个团队最多可以有三个条目,因为这些都是可能的团队状态。如果有更多的条目,首先需要解决gap-n-islands问题,因此查询必须修改为:

select team, case sgn when  1 then 'increasing ' 
                      when -1 then 'decreasing '
                      else 'stable '
             end ||'for '||cnt||' period(s)' status
from (
  select team, dt, sgn, rn, count(1) over (partition by team, grp, sgn order by dt) cnt
  from (
    select team, dt, sgn, rn, sum(grp) over (partition by team order by dt) grp
    from (
      select team, dt, sgn, rn,  
             case when sgn <> lag(sgn) over (partition by team order by dt) 
             then 1 else 0 end grp
      from ( 
        select team, dt, rating, 
               sign(rating - lag(rating) over (partition by team order by dt)) sgn, 
               row_number() over (partition by team order by dt desc) rn 
        from teams ) t1) t2 ) t3 ) t4 
  where rn = 1

所以它只计算最后一个连续的系列。
dbfiddle demo

rekjcdws

rekjcdws2#

最后,我回答了一个稍微更一般的案例,没有将比较限制在3个时期。
这造成了一种困难的情况,其中具有评级,例如增加,然后暂停(或减少),然后再次增加要求我们忽略第一次增加,只保留最后一次。

WITH T(Team, Date, Sign) AS (
    SELECT Team, Date, SIGN(Rating - LAG(Rating) OVER (PARTITION BY Team ORDER BY date))
    FROM MyTable
), LastChange(Team, Date) AS(
    SELECT Team, LEAST(COALESCE(MAX(Date) FILTER (WHERE Sign >= 0),''), COALESCE(MAX(Date) FILTER (WHERE Sign <= 0),''))
    FROM T
    GROUP BY Team
)
SELECT T1.Team,
       CASE T1.Sign WHEN 1 THEN 'Increasing' ELSE 'Decreasing' END AS Direction,
       COUNT(T2.*) AS ForLastConsecutive
FROM T T1
JOIN T T2 
    ON  T1.Team = T2.Team
    AND T1.Sign * T2.Sign > 0
JOIN LastChange ON T2.Team = LastChange.Team AND T2.Date >= LastChange.Date
WHERE T1.Date = '2023-03'
GROUP BY T1.Team, T1.Sign
ORDER BY Team

我让您将最后2列合并为status列。
说明:

  1. 2个连续评分的差值的符号为您提供了变化的方向。在主查询中,当将连接表中的2个符号相乘时,我们希望保留1 * 1-1 * -1,两者都是> 0
    这个产品大大简化了JOIN标准。
    1.第二个技巧是CTE LastChange,它将找到最后一次增加和最后一次减少的日期,并且在两者之外,将保留较早的日期。
    请注意,如果一个团队的评级一直在增加,那么COALESCE(MAX(Date) FILTER (WHERE Sign <= 0)返回的日期将是NULL(如果它一直在减少,则是对称的)。为了让LEAST()正常工作,我们需要一个COALESCE
    因为您的列date实际上是一个文本(缺少日期),所以我使用了''。如果是实际日期,则需要使用-infinity
    您可以尝试使用下面的示例,其中我为team_3添加了1个值:
WITH MyTable(Team,date,rating) AS (
    VALUES
    ('team_1','2022-09',7.5),
    ('team_1','2022-12',8),
    ('team_1','2023-03',8.5),
    ('team_2','2022-09',8),
    ('team_2','2022-12',7),
    ('team_2','2023-03',8),
    ('team_3','2022-06',9),
    ('team_3','2022-09',8),
    ('team_3','2022-12',8),
    ('team_3','2023-03',7),
    ('team_4','2022-09',9),
    ('team_4','2022-12',8),
    ('team_4','2023-03',7)
), ...

相关问题