时间戳

首页 > 解决方案 > How can I create a View in Oracle with a String column whose content is aggregated from numeric values of related entities?

sql - How can I create a View in Oracle with a String column whose content is aggregated from numeric values of related entities?

问题描述

In a Java application based on an Oracle SQL database, I have the following problem to solve:

An entity DataDelivery can consist of any number of Packet. Packet has a ReportDate and the the foreign key datadeliveryid.

ReportDate is of Datatype NUMBER(4,0) and contains the year (YYYY) for example 2020, or 2018. The column ReportDate is in in table PACKET.

In my view, I want to display the DataDelivery as table rows including a column that displays the combined report date (YYYY) of all pertaining packets. The combined ReportDate of the Packets shall be a comma separated list.

Important: When a year such as '2018' occurs multiple times, it shall not be repeated.

So, a DataDelivery with 3 Packets as follows:

DataDelivery
    PACKET1, ReportDate:2020
    PACKET2, ReportDate:2020
    PACKET3, ReportDate:2018
    PACKET4, ReportDate: `null`

shall be displayed as:    2018, 2020

That means, null must be ignored, 2020 must not be repeated. And the values must be sorted in ascending order.

If all packets have a null value as ReportDate, the value in the view must be empty.

For simplicity's sake, I ommitted some of the complexity of my problem including several joins, etc. Also, the dates can only be 2018, 2020, or null, although it would be nicer to remove this constraint. Perhaps someone can suggest a solution based on what I tried here:

            CASE
                WHEN COUNT(CASE eqpaket.berichtszeitraum WHEN 2020 THEN 2020 END) = COUNT(*) THEN '2020'
                WHEN COUNT(CASE eqpaket.berichtszeitraum WHEN 2018 THEN 2018 END) = COUNT(*) THEN '2018'
                WHEN COUNT(distinct eqpaket.berichtszeitraum) > 1 THEN
                    listagg(eqpaket.berichtszeitraum, ',') within group(order by datenlieferungid)
                ELSE ''
            END AS berichtszeitraum,

This works great, when all ReportDates are the same. When they differ, I get duplicates. In the example above I would get: 2018, 2020, 2020. Those duplicates need to be removed, and this must happen within the CASE -- END, if I stick to this approach.

标签： sqloracleoracle11glistagg

解决方案

分两步进行（因为您的数据库版本不支持listaggwith distinct）：

SQL> with datedelivery (datedeliveryid, packet, reportdate) as
  2    -- sample data
  3    (select 1, 'packet1', 2020 from dual union all
  4     select 1, 'packet2', 2020 from dual union all
  5     select 1, 'packet3', 2018 from dual union all
  6     select 1, 'packet4', null from dual union all
  7     --
  8     select 2, 'packet5', 2017 from dual union all
  9     select 2, 'packet6', 2017 from dual union all
 10     select 2, 'packet7', null from dual
 11    )
 12  -- query you need
 13  select datedeliveryid,
 14         listagg(reportdate, ', ') within group (order by reportdate) years
 15  from (-- first find DISTINCT values, then aggregate them
 16        select distinct datedeliveryid, reportdate
 17        from datedelivery
 18       )
 19  group by datedeliveryid;

DATEDELIVERYID YEARS
-------------- --------------------
             1 2018, 2020
             2 2017

SQL>

推荐阅读