sql - 如何使用日期打破总和结果的平局？

Question

我有一个名为referrals 的表，其中包含以下列:

编号 |事件 |模块 |团队 |转介|推荐人 |创建于

为了获得排名，我尝试对 referred_by 的唯一出现次数求和，然后对它们进行排名。这似乎在一定程度上起作用，但是，当出现平局时，我发现排名会重复。相反，我需要的是还要说明推荐发生的日期(最早的日期应该排在第一位，打破平局)。

这是我的原始查询示例:

SELECT referral.referred_by AS id,
          SUM(1) AS referral_count,
          RANK() OVER (ORDER BY referral.count DESC) AS current_position

  FROM referral

  WHERE referral.campaign = 106 AND
        referral.team = 36 AND
        DATE_PART('month', referral."createdAt") = DATE_PART('month', NOW()) AND
        DATE_PART('year', referral."createdAt") = DATE_PART('year', NOW())

  GROUP BY referral.referred_by
  ORDER BY referral_count DESC LIMIT 20

SELECT date_trunc(r."createdAt"), r.referred_by, count(*)

哪些 yield :

id  |   referral_count  |   current_position

894 |   3               |   1

895 |   2               |   2

896 |   2               |   2

897 |   1               |   4

理想情况下应该是:

id  |   referral_count  |   current_position

894 |   3               |   1

895 |   2               |   2

896 |   2               |   3

897 |   1               |   4

谢谢!

Answer 1

您应该能够将 created_at 添加到您的订单中

SELECT referral.referred_by AS id,
          SUM(1) AS referral_count,
          RANK() OVER (ORDER BY referral.count DESC,referral."createdAt") AS current_position

  FROM referral

  WHERE referral.campaign = 106 AND
        referral.team = 36 AND
        DATE_PART('month', referral."createdAt") = DATE_PART('month', NOW()) AND
        DATE_PART('year', referral."createdAt") = DATE_PART('year', NOW())

  GROUP BY referral.referred_by
  ORDER BY referral_count DESC LIMIT 20

SELECT date_trunc(r."createdAt"), r.referred_by, count(*)