按每周日期周期对用户进行排名，并列出所有排名第一的用户

Question

我有一个名为coupon的表，schema如下:

CREATE TABLE "public"."coupons" (
   "id" int4 NOT NULL,
    "suprise" bool NOT NULL DEFAULT false,
    "user_id" int4 NOT NULL,
    "start" timestamp NOT NULL,
    "win_price" numeric(8,2) NOT NULL DEFAULT 0::numeric,
    "fold" int4 NOT NULL DEFAULT 3,
    "pay" numeric(8,2) NOT NULL DEFAULT 0::numeric,
    "rate" numeric(8,2) NOT NULL DEFAULT 0::numeric,
    "win" varchar(255) NOT NULL DEFAULT 'H'::character varying COLLATE "default",
    "end" timestamp NOT NULL,
    "win_count" int4 NOT NULL DEFAULT 0,
    "match_count" int4 NOT NULL DEFAULT 0,
    "played" bool NOT NULL DEFAULT false,
    "created_at" timestamp NOT NULL,
    "updated_at" timestamp NOT NULL
)
WITH (OIDS=FALSE);

为了对win_price weekly的用户进行排名，我编写了下面的查询以获得27-07-2015和03-08-2015之间的前5名:

SELECT ROW_NUMBER() OVER(ORDER BY sum(win_price) DESC) AS rnk,
       sum(win_price) AS win_price, user_id,
       min(created_at) min_create
FROM coupons
WHERE played = true AND win = 'Y'
AND created_at BETWEEN '27-07-2015' AND '03-08-2015'
GROUP BY user_id
ORDER BY rnk ASC
LIMIT 5;

我正在寻找一个新的查询，该查询列出了每周但在给定日期期间排名第一的用户。
即:2015年09月01日至2015年09月30日期间:

<>之前RNK—win_price—user_id—min_create1 - 1.52 - 1 - ...........(第一周)1 - 10.92 - 2 - ...........(发送)1 - 11.23 - 1 - ...........(第三周，依此类推)

Answer 1

SELECT *
FROM  (
   SELECT date_trunc('week', created_at) AS week
        , rank() OVER (PARTITION BY date_trunc('week', created_at)
                       ORDER BY sum(win_price) DESC NULLS LAST) AS rnk
        , sum(win_price) AS win_price
        , user_id
        , min(created_at) min_create
   FROM   coupons
   WHERE  played = true
   AND    win = 'Y' AND created_at BETWEEN '27-07-2015' AND '03-08-2015'
   GROUP  BY 1, 4  -- reference to 1st & 4th column
   ) sub
WHERE  rnk = 1
ORDER  BY week;

返回每周获胜用户 - sum(win_price)最大的用户。

我使用rank()而不是row_number()，因为您没有为每周的多个获胜者定义决胜局。

添加的子句NULLS LAST防止NULL值按降序在顶部排序(DESC) -如果你应该有NULL。看到:

• 按列ASC排序，但首先是NULL值?

星期由开始时间戳表示，您可以使用to_char()以任何您喜欢的方式格式化。

这个查询的关键特性:您可以使用窗口函数而不是聚合函数。看到:

• Postgres窗口函数并按异常分组

考虑SELECT查询中的事件顺序:

• 在应用LIMIT之前获得结果计数的最佳方法