时间戳

首页 > 解决方案 > 根据 CTE 的数字获取排名

postgresql - 根据 CTE 的数字获取排名

问题描述

我在 PostgreSQL 11 中有一个复杂的情况，我需要根据从 CTE 获得的单个数字生成编号。

下面是CTE

 WITH pending_orders_to_be_processed_details
    AS
    (
    SELECT ROW_NUMBER() OVER(ORDER BY so.create_date ) as queue_no
, name,so.create_date ::TIMESTAMP
    FROM  picking sp
    LEFT JOIN order so ON so.name=sp.origin
    WHERE sp.state IN('assigned','confirmed')
     )
    ,orders_which_can_be_processed_today AS
    (
    -- This CTE will give me a count of orders 
and its hourly average, Lets say count is 400 and hourly avg is 3

    )

现在我需要根据每小时的平均值对细节进行编号，意味着前 3 个订单需要排名为 1，接下来的 3 个订单需要排名为 2，依此类推，这样我就可以确定这些可以根据这个排名。

输入将是

name         queu_number.  create_date
    so1            1       2021-03-11 12:00:00
    so2            2       2021-03-11 13:00:00
    so3            3       2021-03-11 14:00:00
    so4            4       2021-03-11 15:00:00
    so5            5       2021-03-11 16:00:00
    so6            6       2021-03-11 17:00:00
    so7            7       2021-03-11 18:00:00
    so8            8       2021-03-11 19:00:00
    so9            9       2021-03-11 20:00:00

预期的输出将是

name         rank
so1            1
so2            1
so3            1
so4            2
so5            2
so6            2
so7            3
so8            3
so9            3

任何帮助/建议。

标签： postgresql

解决方案

编辑：我最近了解了一个非常适合这里的函数：

演示：db<>小提琴

您可以为此使用ntile()窗口函数：

SELECT
    *,
    ntile(3) OVER (ORDER BY create_date)
FROM mytable

演示：db<>小提琴

由于您已经创建了累积行数，您可以使用它来创建您的预期排名：

SELECT
    *,
    floor((queue_no - 1) / 3) + 1 as rank
FROM my_cte
  1. queue_no - 1（所以，1to3将被转移到0to 2）
  2. 区别3：所以，0将2是0.x和3将5是1.x，...
  3. 现在将这些结果四舍五入为0, 1, 2, ...
  4. 如果您想以1代替开头0，请添加1

推荐阅读