返回x个元素，按非唯一sql属性排序

okxuctiv 于 2021-08-09 发布在 Java

关注(0)|答案(4)|浏览(374)

我正在尝试编写一个查询，该查询返回给定实体前后的x个元素，该实体按不唯一的属性排序。
例如：属性 a 是主列（唯一的uuid）， b 我想按哪个属性排序

table
  --------
  a      b
  --------
  ag     1     
  sb     1
  sf     1
  xk     2
- bd     2
  ve     2
  ku     2
  lt     3
  ac     3

如果我想返回a= bd 排序依据 b 之前

SELECT * FROM table WHERE b >= 2 ORDER BY b DESC, a DESC LIMIT x

之后

SELECT * FROM table WHERE b <= 2 ORDER BY b ASC, a DESC OFFSET 1 LIMIT x

如果b的属性是唯一的，这就行了。如何在非唯一属性上执行此操作。

sql postgresql window-functions sql-order-by sql-limit

来源：https://stackoverflow.com/questions/62353387/return-x-elements-before-and-after-sorted-by-a-property-that-is-not-unique-sql

4条答案

按热度按时间

6g8kf2rb1#

可以使用窗口函数：

select t.*
from (select t.*,
             count(*) filter (where a = 'bd') over (order by b, a rows between x preceding and x following) as cnt
      from t
     ) t
where cnt > 0;

这是一把小提琴。

赞(0）回复(0）举报 2021-08-09

7vhp5slm2#

这是一个执行窗口的实现

WITH
    data AS (SELECT * FROM id_value),
    before AS (
        SELECT * FROM id_value
            WHERE VALUE < (SELECT VALUE FROM id_value WHERE id = ID)
            ORDER BY VALUE DESC, id DESC
            limit 1200
    ),
    after AS (
        SELECT * FROM id_value
            WHERE VALUE >= (SELECT VALUE FROM id_value WHERE id = ID)
            ORDER BY VALUE ASC, id ASC
            limit 1200
    ),
    windowed AS (
        (SELECT * FROM before)
        UNION ALL
        (SELECT * FROM after)
    )

SELECT t.*
FROM (
    SELECT *,
             COUNT(*) filter (
                 WHERE id = ID
             ) over (
                 ORDER BY VALUE, id rows BETWEEN 5 preceding AND 5 following
             ) AS cnt
    FROM windowed
) t
WHERE cnt > 0;

赞(0）回复(0）举报 2021-08-09

zpjtge223#

你可以的 row_number() 具体如下：

select a, b
from (
    select t.*, max(rn) filter(where a = 'bd') over() target_rn
    from (
        select t.*, row_number() over(order by b, a) rn
        from mytable t
    ) t
) t
where abs(rn - target_rn) <= 2

赞(0）回复(0）举报 2021-08-09

mi7gmzs64#

如果向前和向后的行数不是一个固定的数字，您可能会发现这样的方法很有用。

with
    data as (select * from t /* or filter results here if complicated */),
    bck as (
        select *, dense_rank() over (order by b desc) grp
        from data where b < (select b from t where a = 'bd')
        -- where /* try filtering here first */
        order by b desc
        limit 100
    ),
    fwd as (
        select *, dense_rank() over (order by b) grp
        from data where b >= (select b from t where a = 'bd')
        -- where /* try filtering here first */
        order by b
        limit 100
    ) 
select * from bck where grp  = 1
union all
select * from fwd where grp <= 2
order by b;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a5318a7872bf5e7dc347a00ea4b9f3fc

赞(0）回复(0）举报 2021-08-09

我来回答

返回x个元素，按非唯一sql属性排序

4条答案

相关问题

热门标签

最新问答