主页 / dba / 问题 / 307440
Accepted
Healyhatman
Asked: 2022-02-14 16:26:25 +0800 CST

获取由具有连续值计数的外键分组的行

  • 772

我有一个带有 、 和 的表的transactionsSQL Server 数据库。client_iddateis_cancelled

我正在尝试获取在标记为 is_cancelled的行中有 3 个或更多事务的 client_ids ，以及 in_a_row 计数。我已经得到了以下内容，当 is_cancelled 支持是连续的时，is_same 的值为 1，并且取消的事务总数为 1（这不是我所需要的）

SELECT 
client_id,
date,
is_same,
SUM(is_same) OVER (PARTITION BY client_id ORDER BY date) AS sum_same,
transCancelled
FROM
(
    SELECT
    client_id,
    LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date) AS previous_cancelled,
    CASE 
        WHEN is_cancelled = LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date)
        THEN 1
        ELSE 0
    END as is_same,
    date,
    is_cancelled
    FROM transactions
    WHERE deleted_at IS NULL -- Ignore soft-deleted rows
) AS t_01
WHERE previous_cancelled = 1
ORDER BY date

摆弄示例数据：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a0c9b12203ab2d0c83f73604ccc9d0a0

预期数据（client_id，count） 1, 3 3, 6

sql-server window-functions

1 个回答

  1. Best Answer

    这是一种差距和孤岛问题。

    这种类型的大多数解决方案的关键是计算更改，因此您需要一is_different列，而不是is_same. 然后您有条件地计算该列（如果您使用NULL而不是更容易0）为每组行创建一个 ID。

    目前尚不清楚您到底想要什么最终结果，但通过对该结果进行分组，您可以获得连续行的最大和最小数量，以及实际行组的计数，每个client_id：

    WITH PrevValues AS (
    SELECT
      client_id,
      LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date) AS previous_cancelled,
      CASE WHEN is_cancelled = LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date)
        THEN NULL ELSE 1 END
        as is_different,
      date,
      is_cancelled
    FROM transactions
),
Grouped AS (
    SELECT 
      client_id,
      date,
      is_different,
      COUNT(is_different) OVER (PARTITION BY client_id ORDER BY date ROWS UNBOUNDED PRECEDING) AS group_id
    FROM PrevValues
),
ByGroups AS (
    SELECT
      client_id,
      COUNT(*) as in_a_row
    FROM Grouped
    GROUP BY
      client_id,
      group_id
    HAVING COUNT(*) >= 3
)
SELECT
  client_id,
  MAX(in_a_row) as max_in_a_row,
  MIN(in_a_row) as min_in_a_row,
  COUNT(*) as num_groups
FROM ByGroups
GROUP BY
  client_id;

    db<>小提琴

    请注意，您的示例数据具有相同日期的行，这是您应该始终使用的原因之一ROWS UNBOUNDED PRECEDING（有序窗口函数的默认值RANGE UNBOUNDED PRECEDING是略有不同的）。无论如何，您应该始终尝试确定性排序。

    • 4

相关问题