主页 / dba / 问题 / 235306
Accepted
codigofontes
Asked: 2019-04-21 04:23:59 +0800 CST

显示当前行“连胜”

  • 772

我需要在查询中显示每行的连续赢/输，所以给定下表，查询应该返回“预期”列。我尝试了一些使用窗口函数的方法，但没有成功。

create table matches (player text, dt date,  is_winner boolean, expected integer )
insert into matches values
('A', '2019-01-01', TRUE, 0),
('A', '2019-01-03', TRUE, 1),
('A', '2019-01-04', TRUE, 2),
('A', '2019-01-09', FALSE, 0),
('A', '2019-01-10', FALSE, -1),
('A', '2019-01-15', TRUE, 0);

player  dt          is_winner   expected
A       2019-01-01  true        0
A       2019-01-03  true        1
A       2019-01-04  true        2
A       2019-01-09  false       0
A       2019-01-10  false       -1
A       2019-01-15  true        0

逻辑是：

  1. 输后赢或赢后输时重置为 0。
  2. 获胜后增加，但如果是案例 1，则不会增加。
  3. 亏损后递减，但如果是第一种情况则不会。

欢迎任何有关如何解决此问题的见解。我最后的手段是每行都调用一个循环的函数。

postgresql gaps-and-islands

1 个回答

  1. Best Answer

    我已经使用 CTE 分阶段完成了这项工作，以便您可以在查询进行时看到它是如何完成的。每个 CTE 在输出中添加一列以显示进度。

    老实说，它几乎是用 CTE 名称自我记录的。

    with lags as (
  select player,
         dt,
         is_winner,
         lag(is_winner) OVER (partition by player ORDER BY dt ASC) as prev_is_winner,
         expected
  from matches
),
     group_changes as (
       select lags.*,
              case
                when prev_is_winner <> is_winner or prev_is_winner is null
                  then 1
                else 0
                end as is_new_group
       from lags
     ),
     groups_numbered as (
       select *,
              sum(is_new_group)
                  over (partition by player order by dt, is_winner desc) as streak_group
       from group_changes
     ),
     expected_in_groups as (
       select groups_numbered.*,
              row_number()
                  over (partition by player,streak_group
                    order by dt asc, streak_group asc) - 1 as expected_unsigned
       from groups_numbered
     )
select expected_in_groups.*, case when is_winner = 't' then expected_unsigned else expected_unsigned * -1 end as actual
from expected_in_groups
order by player asc, dt asc;

    DB Fiddle Link（我添加了一个额外的行只是为了确保它在某个点工作）

    基本上：

    1. lagsCTE：用于LAG()获取相对于当前行的上一个结果。
    2. group_changesCTE：检测之前的连胜，无论输赢，是否已经结束
    3. groups_numberedCTE：给每个连胜一个数字
    4. expected_in_groupsCTE：对组中的行进行编号
    5. 决赛select：否定连败

    .

    +--------+------------+-----------+----------------+----------+--------------+--------------+-------------------+--------+
| player |     dt     | is_winner | prev_is_winner | expected | is_new_group | streak_group | expected_unsigned | actual |
+--------+------------+-----------+----------------+----------+--------------+--------------+-------------------+--------+
| A      | 2019-01-01 | t         |                |        0 |            1 |            1 |                 0 |      0 |
| A      | 2019-01-03 | t         | t              |        1 |            0 |            1 |                 1 |      1 |
| A      | 2019-01-04 | t         | t              |        2 |            0 |            1 |                 2 |      2 |
| A      | 2019-01-09 | f         | t              |        0 |            1 |            2 |                 0 |      0 |
| A      | 2019-01-10 | f         | f              |       -1 |            0 |            2 |                 1 |     -1 |
| A      | 2019-01-11 | f         | f              |       -2 |            0 |            2 |                 2 |     -2 |
| A      | 2019-01-15 | t         | f              |        0 |            1 |            3 |                 0 |      0 |
+--------+------------+-----------+----------------+----------+--------------+--------------+-------------------+--------+
    • 6

相关问题