如何使用Oracle SQL为每个月选择一列的选择最大值
问题描述:
我想编制一份报告,显示项目是否在一年的每个月中完成。有多少项目完成并不重要,只是项目是否完成。如何使用Oracle SQL为每个月选择一列的选择最大值
with project_data as(
select '123' account, 'Started' status, to_date('01/01/2017','MM/DD/YY')
sale_date from dual union all
select '123' account, 'Complete' status, to_date('01/15/2017','MM/DD/YY')
sale_date from dual union all
select '123' account, 'Started' status, to_date('02/01/2017','MM/DD/YY')
sale_date from dual union all
select '123' account, 'Complete' status, to_date('04/12/2017','MM/DD/YY')
sale_date from dual union all
select '123' account, 'Complete' status, to_date('04/16/2017','MM/DD/YY')
sale_date from dual)
从这个例子中我想输出到如:
Account Completed Month Year
123 Yes 1 2017
123 No 2 2017
123 No 3 2017
123 Yes 4 2017
答
甲骨文设置:
CREATE TABLE table_name (account, status, sale_date) AS
select '123', 'Started', DATE '2017-01-01' from dual union all
select '123', 'Complete', DATE '2017-01-15' from dual union all
select '123', 'Started', DATE '2017-02-01' from dual union all
select '123', 'Complete', DATE '2017-04-12' from dual union all
select '123', 'Complete', DATE '2017-04-16' from dual union all
select '456', 'Complete', DATE '2017-03-28' from dual;
查询:
SELECT ACCOUNT,
MONTH,
CASE COUNT(CASE status WHEN 'Complete' THEN 1 END)
WHEN 0
THEN 'No'
ELSE 'Yes'
END AS Completed
FROM (SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), LEVEL - 1) AS month
FROM DUAL
CONNECT BY ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), LEVEL - 1) <= SYSDATE)
LEFT OUTER JOIN
table_name t
PARTITION BY (t.account)
ON (month <= sale_date AND sale_date < ADD_MONTHS(month, 1))
GROUP BY Account, Month
ORDER BY Account, Month;
输出:
ACC MONTH COM
--- ------------------- ---
123 2017-01-01 00:00:00 Yes
123 2017-02-01 00:00:00 No
123 2017-03-01 00:00:00 No
123 2017-04-01 00:00:00 Yes
456 2017-01-01 00:00:00 No
456 2017-02-01 00:00:00 No
456 2017-03-01 00:00:00 Yes
456 2017-04-01 00:00:00 No
+0
这很好,谢谢! – mjanach
答
SELECT YEAR,
MONTH,
ACCOUNT,
CASE WHEN COMPLETED > 0 THEN 'YES' ELSE 'NO' END AS AT_LEAST_ONE_COMP
FROM ( SELECT EXTRACT (YEAR FROM SALE_DATE) AS YEAR,
EXTRACT (MONTH FROM SALE_DATE) AS MONTH,
ACCOUNT,
SUM (CASE WHEN STATUS = 'Complete' THEN 1 ELSE 0 END)
AS completed
FROM MY_TABLE
GROUP BY EXTRACT (YEAR FROM SALE_DATE),
EXTRACT (MONTH FROM SALE_DATE),
ACCOUNT)
你需要一个月,是不是出现在你的输入数据的行(在你的榜样,您显示的3个月值的行,但没有排在3月的日期)?如果是这样,你接受的解决方案似乎并没有这样做。所以 - 你的问题陈述是不正确的,还是被接受的答案实际上不是你所需要的? – mathguy
接受的答案并没有做我所需要的一切。我接受它是因为这是一个很好的解决方案,并且会为我工作,但有一项要求错过了。我未接受解决方案并发表评论。谢谢。 – mjanach