sql - 计算直到某个日期的唯一组合 - 每月

Question

我正在查看一个包含买家和卖家的双边平台交易数据的表格。我想知道买家和卖家的独特组合的总量。比方说，安倍在 1 月份从布兰登那里购买，这是 1 个组合。如果 Abe 在 2 月从 Cece 购买，则为 2，但如果 Abe 随后再次从 Brandon 购买，则仍为 2。

我的解决方案是使用 DENSE_RANK() 函数:

WITH
combos AS (
  SELECT
        t.buyerid, t.sellerid,
        DENSE_RANK() OVER (ORDER BY t.buyerid, t.sellerid) AS combinations

    FROM transactions t

    WHERE t.transaction_date < '2018-05-01'
)
SELECT
    MAX(combinations) AS total_combinations

FROM combos

这很好用。每个新组合都会获得更高的排名，如果您选择该结果的最大值，您就会知道独特组合的数量。

但是，我想知道每个月独特组合的总数。这里的问题是，如果我按交易月分组，它只计算该月的唯一组合。在 Abe 的示例中，它在一月份是一个独特的组合，然后在下个月是另一个组合，因为这就是 SQL 中分组的工作方式。

例子:

transaction_date  buyerid  sellerid
2018-01-03        3828     219
2018-01-08        2831     123
2018-02-10        3828     219

所有这些行的 DENSE_RANK() 命名组合的输出是:

transaction_date  buyerid  sellerid  combinations
2018-01-03        3828     219       1
2018-01-08        2831     123       2
2018-02-10        3828     219       2

因此，在选择 MAX 组合时，您会知道此处显示的唯一买家/卖家组合的数量。

但是，我希望看到直到每个月初，直到现在所有月份的独特组合的运行总数。但是，当我们按月分组时，它会是这样的:

transaction_date  buyerid  sellerid  month combinations
2018-01-03        3828     219       jan   1
2018-01-08        2831     123       jan   2
2018-02-10        3828     219       feb   1

虽然我实际上想要这样的输出:

month  total_combinations_at_month_start
jan    0
feb    2
mar    2

我该如何解决？我试图找到有关各种窗口函数的帮助，但直到现在都没有运气。谢谢!

Answer 1

这是一种方法:

WITH combos AS (
      SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY sellerid, buyerid ORDER BY t.transaction_date) as combo_seqnum,
             ROW_NUMBER() OVER (PARTITION BY sellerid, buyerid, date_trunc('month', t.transaction_date) ORDER BY t.transaction_date) as combo_month_seqnum
      FROM transactions t

      WHERE t.transaction_date < '2018-05-01'
    )
SELECT 'Overall' as which, COUNT(*)
FROM combos
WHERE combo_seqnum = 1
UNION ALL
SELECT to_char(transaction_date, 'YYYY-MM'), COUNT(*)
FROM combos
WHERE combo_month_seqnum = 1
GROUP BY to_char(transaction_date, 'YYYY-MM');

这会将结果放在单独的行中。如果你想要一个累计数和每个月的数:

SELECT to_char(transaction_date, 'YYYY-MM'),
       SUM( (combo_month_seqnum = 1)::int ) as uniques_in_month,
       SUM(SUM( (combo_seqnum = 1)::int )) OVER (ORDER BY to_char(transaction_date, 'YYYY-MM')) as uniques_through_month
FROM combos
GROUP BY to_char(transaction_date, 'YYYY-MM')

Here是说明解决方案的 rextester。