代码之家 › 专栏 › 技术社区 › armandino

Oracle:LISTAGG排除第一行

oracle sql

armandino · 技术社区 · 2 年前

我有以下查询,用于连接给定id的值:

SELECT LISTAGG(DISTINCT(t.VALUE), ' - ')
  WITHIN GROUP (ORDER BY t.CREATED_DATE DESC)
  FROM MY_TABLE t
  WHERE t.id=?
  GROUP BY t.id;

如何修改此查询以排除第一个(即最近的)值?

如果有2条记录,则只应返回一个值
如果只有1条记录,则应返回null
如果所有值都相同,则应返回null

3 回复 | 直到 2 年前

MT0 2 年前

使用 RANK 用于过滤第一行的分析函数:

SELECT LISTAGG(DISTINCT value, ' - ')
         WITHIN GROUP (ORDER BY created_date DESC) AS value_list
FROM   (
  SELECT value,
         created_date,
         RANK() OVER (ORDER BY created_date DESC) AS rnk
  FROM   MY_TABLE
  WHERE  id = ?
)
WHERE  rnk > 1;

注意:如果你想 UNIQUE 值,然后 等级 (或 DENSE_RANK )当有两行或多行与最新创建的日期绑定时,分析函数将过滤出最新创建日期;如果您使用 ROW_NUMBER 分析函数,那么当存在联系时,您仍然可以获得最新创建日期的实例。

对于样本数据:

CREATE TABLE my_table (id, value, created_date) AS
SELECT 1, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 2, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 2, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 3, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 3, 'B', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 4, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 4, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 4, 'B', DATE '2023-01-02' FROM DUAL UNION ALL
SELECT 4, 'C', DATE '2023-01-03' FROM DUAL UNION ALL
SELECT 4, 'B', DATE '2023-01-04' FROM DUAL UNION ALL
SELECT 5, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 5, 'B', DATE '2023-01-02' FROM DUAL;

然后,当绑定变量为1、2或3时,输出为:

VALUE_LIST
无效的

当绑定变量为4时,输出为:

VALUE_LIST
C-B-A

当绑定变量为5时,输出为:

VALUE_LIST
A.

fiddle

更新:

根据OP的注释,当存在具有该值和最新创建日期的任何行时,他们似乎希望筛选值。在这种情况下,您可以:根据进行筛选 id 第一按值进行聚合,以找到每个值的最新日期和整个结果集的最大日期;然后过滤以忽略最新日期;并与聚合 LISTAGG :

SELECT LISTAGG(value, ' - ')
         WITHIN GROUP (ORDER BY created_date DESC) AS value_list
FROM   (
  SELECT value,
         MAX(created_date) AS created_date,
         MAX(MAX(created_date)) OVER () AS max_created_date
  FROM   MY_TABLE
  WHERE  id = ?
  GROUP BY value
)
WHERE  created_date < max_created_date;

fiddle

Austin 2 年前

创建一个CTE来标记最近的值,然后添加 where 不选择它的条件:

with my_table_flagged as
(
    select --other values from table
        , row_number() over (partition by t.ID order by t.CREATED_DATE desc) rn
    from my_table t
)
SELECT LISTAGG(UNIQUE(t.VALUE), ' - ')
  WITHIN GROUP (ORDER BY t.CREATED_DATE DESC)
  FROM my_table_flagged t
  WHERE t.id=?
  AND rn <> 1
  GROUP BY t.id;

Hogan 2 年前

这似乎是正确的答案

WITH numbered AS 
(
  SELECT x.*, 
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY t.CREATED_DATE DESC) as RN
  FROM MY_TABLE x
)  
SELECT LISTAGG(UNIQUE(t.VALUE), ' - ')
  WITHIN GROUP (ORDER BY t.CREATED_DATE DESC)
FROM numbered t 
WHERE t.id=? AND RN > 1
GROUP BY t.id;

但是,对于只有一行的项,这不会给您null。为此,您需要这样做:

WITH numbered AS 
(
  SELECT x.*, 
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY t.CREATED_DATE DESC) as RN
  FROM MY_TABLE x
)  
SELECT LISTAGG(UNIQUE(x.VALUE), ' - ')
  WITHIN GROUP (ORDER BY COALESCE(t.CREATED_DATE,NOW()) DESC)
FROM (
  SELECT DISTINCT id
  FROM MY_TABLE
) X 
LEFT JOIN numbered t ON t.id = X.id AND t.RN > 1
WHERE x.id = ?
GROUP BY x.id;