Oracle:LISTAGG不包括第一行

wd2eg0qa  于 2023-04-20  发布在  Oracle
关注(0)|答案(3)|浏览(129)

我有以下查询,它连接给定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
dbf7pr2w

dbf7pr2w1#

使用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值,则RANK(或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|
| --------------|
| 联系我们|
当bind变量为4时,输出为:
| VALUE_LIST|
| --------------|
| C - B - A|
当bind变量为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

qvsjd97n

qvsjd97n2#

创建一个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;
oipij1gg

oipij1gg3#

这似乎是正确的答案

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;

相关问题