主页 / dba / 问题 / 294491
Accepted
Daniel Rearden
Asked: 2021-06-19 04:50:04 +0800 CST

通过重复模式对行进行排序

  • 772

给定 Postgres 中的表，例如

CREATE TABLE t1 (
  type text NOT NULL,
  value int NOT NULL
)

where typecould be A, Bor Cand 给定一个任意的重复模式，比如

A B B C B B

如何查询表，以便结果首先根据模式排序，然后根据值排序？换句话说，第一行是A最低value的，第二行是B最低value的，第三行是B第二低的value，依此类推。如果我们用完了特定的类型，我们就继续前进到模式中的下一个字母（即，如果我们没有更多C的 s 要返回，模式就变成ABBBB）。

类型 价值
一个 1
乙 1
乙 2
C 1
乙 3
乙 4
一个 2
乙 5
乙 6
乙 7
乙 8
一个 3
乙 9

实际模式是动态的并且具有任意长度，认为它总是被限制为只有type.

这是一个样本数据的小提琴。

postgresql order-by

1 个回答

  1. Best Answer

    使用普通 SQL 并不容易，但仍然可以：

    WITH pattern AS  (
   SELECT *, row_number() OVER (PARTITION BY type ORDER BY ord) AS pos
   FROM   unnest('{A,B,B,C,B,B}'::text[]) WITH ORDINALITY t(type, ord)
              -- provide pattern here
   ) 
 , type_frequency AS (
   SELECT type, count(*) AS freq
   FROM   pattern
   GROUP  BY 1
   )
SELECT type, t.value -- , t.epoch, p.ord, pos
FROM  (
   SELECT type, value
        , ceil(rn / freq::float) AS epoch
        , (rn - 1) % freq + 1    AS pos
   FROM  (
      SELECT *, row_number() OVER (PARTITION BY type ORDER BY value) AS rn
      FROM   t1  -- base table here
      JOIN   type_frequency tf USING (type)
      ) sub
   ) t
JOIN   pattern p USING (type, pos)
ORDER  BY t.epoch, p.ord;

    db<>在这里摆弄

    产生您想要的结果。

    使用循环遍历模式并从表中获取每种类型的下一个更大值的程序解决方案似乎更简单。但是这个查询应该仍然表现得不错。

    使用说明

    在第一个 CTE 中提供一次任何pattern模式。假设 base type text，但任何支持相等的类型都有效。UnnestWITH ORDINALITY用于保留模式 ( ord) 中元素的原始顺序。提炼每种类型的位置 ( pos) 以便稍后与表行匹配。关于WITH ORDINALITY：

    第二个 CTEtype_frequency计算模式中的不同值及其各自的频率。

    在外部查询中，在子查询中sub，加入到type_frequency以过滤仅涉及的类型，同时添加频率，并为rn每个类型添加一个行号（ ）。

    将其除以rn频率值freq会产生epoch, 行的主要顺序。（不能在这里使用整数除法，这会截断，所以转换为float！）
    rn 模（%） freq产生pos每种类型的位置。向后移动 ( - 1) 和向前移动 ( + 1) 以修复偏离 1 的问题。我们需要一个整数类型来进行模运算。

    最后，加入模式 on以在模式 per 中(type, pos)附加原始订单 ( ) 。ordepoch

    • 6

相关问题