问题

以下是已翻译的部分：

我有一个相当复杂的查询，但为了完整起见，我将它原样发布：

在这里发生的情况是，我正在对日期列应用滚动窗口，并计算过去30天内的发生次数。
从这些值中，我想要获取每种类型的最大值。

这就是外部语句的作用（这是有趣的部分）。

当然，在计算最大值之前，我有多行，但我想获取列count_30_days的最大值，同时也获取相应的日期。

运行这个查询时，我收到以下错误：

> 单行子查询返回多行。

如有需要，请提出其他问题。

WITH cte AS 
(
    SELECT
        t.ID, t.TYPE, t.DATE, COUNT(*) AS count_30_days
    FROM 
        ASQ_AFTERSALES.TYPE AS t
    INNER JOIN 
        ASQ_AFTERSALES.TYPE i ON t.DATE >= ADD_DAYS (i.DATE, -30)
                              AND t.DATE <= i.DATE
                              AND t.TYPE = i.TYPE
    GROUP BY 
        t.ID, t.TYPE, t.DATE
)
SELECT 
    TYPE, MAX(count_30_days) AS max_incidence
FROM 
    cte
GROUP BY 
    TYPE

WITH cte AS 
(
    SELECT
        t.ID, t.TYPE, t.DATE, COUNT(*) AS count_30_days
    FROM 
        ASQ_AFTERSALES.TYPE AS t
    INNER JOIN 
        ASQ_AFTERSALES.TYPE i ON t.DATE >= ADD_DAYS (i.DATE, -30)
                              AND t.DATE <= i.DATE
                              AND t.TYPE = i.TYPE
    GROUP BY 
        t.ID, t.TYPE, t.DATE
)
SELECT 
    TYPE, MAX(count_30_days) AS max_incidence, DATE
FROM 
    cte
GROUP BY 
    TYPE, DATE

答案1

得分: 1

token发送数量= 69，token接受数量= 46
分析：这段代码中有 2 句交互语言，
思考：其中 "This is what I came up with:" 为指令，其余为程序代码，不需要汉化
推测：部分交互语言只要汉化 "What I am doing is calculating the count for every type in the last 30 days. After that we make a new cte called ranked which numbers all counts (using ROW_NUMBER()) ordered by the quantity. The result is that for every type the highest quantity gets the row number 1. With that you can just select the results which have row number = 1 and you get only the MAX value."
计划：开始汉化从 "What I am doing is calculating..." 到 "...you get only the MAX value." 的交互语言的代码：计算每种类型在过去30天内的计数。然后，我们创建一个名为"ranked"的新的cte，对所有计数进行编号（使用ROW_NUMBER()），按数量排序。结果是，每种类型的最大数量都获得行号1。因此，您只需选择行号= 1的结果，即可获取最大值。

WITH cte AS (
SELECT
    t.ID, 
    t.TYPE, 
    t.DATE, 
    COUNT(*) OVER (PARTITION BY t.TYPE, t.DATE) AS count_30_days
FROM ASQ_AFTERSALES.TYPE AS t
INNER JOIN ASQ_AFTERSALES.TYPE i
    ON t.DATE >= ADD_DAYS(i.DATE, -30)
    AND t.DATE <= i.DATE
    AND t.TYPE = i.TYPE
),

ranked AS (
SELECT 
    TYPE, 
    count_30_days, 
    DATE,
    ROW_NUMBER() OVER (PARTITION BY TYPE ORDER BY count_30_days DESC) as rn
FROM cte

)
SELECT 
TYPE, 
count_30_days AS max_incidence, 
DATE
FROM ranked
WHERE rn = 1

答案2

得分: 1

token发送数量= 126，token接受数量= 49
分析：这段代码中有 0 句交互语言，
思考：其中 "I think you're looking for this. You want to show an additional column for which the aggregated value is the MAX. That can be done in multiple ways, but the easiest is to use the KEEP keyword. Here is a simpler example. There is a table with a TYPE column and a DATE column. I want to show the date for the highest type count.

id number generated by default on null as identity 
constraint sample_id_pk primary key,
type varchar2(30 char),
dt date ) ;
-- load data
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-01' );
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-01' );
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-01' );
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-02' );
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-04' );
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-02' );
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-05' );
insert into sample ( type, dt ) values ( 'B', DATE'2023-07-04' );
insert into sample ( type, dt ) values ( 'B', DATE'2023-07-04' );
insert into sample ( type, dt ) values ( 'B', DATE'2023-07-05' );
insert into sample ( type, dt ) values ( 'B', DATE'2023-07-02' );
insert into sample ( type, dt ) values ( 'B', DATE'2023-07-02' );
insert into sample ( type, dt ) values ( 'B', DATE'2023-07-03' );
WITH cte AS 
( select type, dt, count(*) as cnt from sample group by type, dt )
select type, max(cnt) as cnt, max(dt) keep (dense_rank first order by cnt desc) as dt
from cte
group by type;

TYPE CNT DT
------------------------------ ---------- -----------
A 3 01-JUL-2023
B 2 04-JUL-2023
``` " 为指令， "***" 为程序代码， "***" 为文件名，不能汉化
推测：部分交互语言只要汉化 0 个单词
计划：开始汉化从 "It shouldn't be too hard to apply this example to your dataset.

This is very well explained (along with the other possibilities to solve this issue) in [this video][1] by @Connor McDonald" 开始
scss
适度汉化交互语言的代码： "It shouldn't be too hard to apply this example to your dataset.

This is very well explained (along with the other possibilities to solve this issue) in [this video][1] by @Connor McDonald"

<details>
<summary>英文:</summary>

I think you&#39;re looking for this. You want to show an additional column for wich the aggregated value is the MAX. That can be done in multiple ways, but the easiest is to use the ```KEEP``` keyword. Here is a simpler example. There is a table with a TYPE column and a DATE column. I want to show the date for the highest type count.

create table sample (
id number generated by default on null as identity
constraint sample_id_pk primary key,
type varchar2(30 char),
dt date
)
;

-- load data
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-01' );
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-01' );
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-01' );
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-02' );
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-04' );
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-02' );
insert into sample ( type, dt ) values ( 'A', DATE'2023-07-05' );
insert into sample ( type, dt ) values ( 'B', DATE'2023-07-04' );
insert into sample ( type, dt ) values ( 'B', DATE'2023-07-04' );
insert into sample ( type, dt ) values ( 'B', DATE'2023-07-05' );
insert into sample ( type, dt ) values ( 'B', DATE'2023-07-02' );
insert into sample ( type, dt ) values ( 'B', DATE'2023-07-02' );
insert into sample ( type, dt ) values ( 'B', DATE'2023-07-03' );

WITH cte AS
(
select type, dt, count(*) as cnt from sample group by type, dt
)
select type, max(cnt) as cnt, max(dt) keep (dense_rank first order by cnt desc) as dt
from cte
group by type;

TYPE CNT DT

A 3 01-JUL-2023
B 2 04-JUL-2023

It shouldn't be too hard to apply this example to your dataset.

This is very well explained (along with the other possibilities to solve this issue) in [this video][1] by @Connor McDonald


  [1]: https://www.youtube.com/watch?v=AlTI_ZUyE0U

</details>