oracle PLSQL：在单个查询中获取一周中每一天的总和以及一周的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/775787/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PLSQL : Get sum for each day of week and total sum for week in a single query
提问by Learning
Let's say , I have a table, ClientTrade, like thus :
假设，我有一个表 ClientTrade，如下所示：
ClientName , TradeDate , Quantity
And I want to create a query in Oracle PLSQL which should return the result like this : (The days are derived from the TradeDate column and Mon = sum(Quantity) for Mon , Tue = sum(Quantity) for Tue ... etc.)
我想在 Oracle PLSQL 中创建一个查询，它应该返回这样的结果：（天数来自 TradeDate 列，Mon = sum(Quantity) for Mon，Tue = sum(Quantity) for Tue ...等。 )
ClientName Mon Tue Wed Thu Fri Sat Sun TotalForWeek
ABC 10 15 5 2 4 0 0 34
XYZ 1 1 2 1 2 0 0 7
Assuming that this report will always have where conditions which make it run for one week , is this possible to create this in a single query?
假设此报告始终具有使其运行一周的 where 条件，是否可以在单个查询中创建此报告？
回答by Jeffrey Kemp
Just simplifying a bit...
稍微简化一下...
SELECT ClientName,
SUM(CASE WHEN to_char(TradeDate,'DY')='MON' THEN Quantity ELSE NULL END) AS Mon,
SUM(CASE WHEN to_char(TradeDate,'DY')='TUE' THEN Quantity ELSE NULL END) AS Tue,
SUM(CASE WHEN to_char(TradeDate,'DY')='WED' THEN Quantity ELSE NULL END) AS Wed,
SUM(CASE WHEN to_char(TradeDate,'DY')='THU' THEN Quantity ELSE NULL END) AS Thu,
SUM(CASE WHEN to_char(TradeDate,'DY')='FRI' THEN Quantity ELSE NULL END) AS Fri,
SUM(CASE WHEN to_char(TradeDate,'DY')='SAT' THEN Quantity ELSE NULL END) AS Sat,
SUM(CASE WHEN to_char(TradeDate,'DY')='SUN' THEN Quantity ELSE NULL END) AS Sun,
SUM(Quantity) AS TotalForWeek
FROM ClientTrade
GROUP BY ClientName
回答by tpdi
Subqueries.
子查询。
select ClientName,
(select sum(b.quantity)
from table b where b.clientName = a.clientname
and b.tradedate = [some constant or calculation that identifies monday])
as Mon,
(select sum(b.quantity)
from table b where b.clientName = a.clientname
and b.tradedate = [some constant or calculation that identifies tuesday])
as Tue,
..etc..
from table a
A cleaner, but possibly less efficient way involves a view with a group by:ew
一种更简洁但可能效率较低的方法是通过以下方式查看一组视图：ew
create view quantityperday as
select clientname,
tradedate,
dayofweek(tradedate) as dow,
weekofyear(tradedate) as woy,
year(tradedate) as y,
sum(quantity) as quantity
from table
group by clientname, tradedate;
Then:
然后：
select clientname, b.quantity as Mon, c.quantity as Tue ....
from table a join quantityperday b
on (a.clientname = b.clientname and b.y = '2008'
and b.doy = 2 and b.dow = 'Monday')
quantityperday c
on (a.clientname = c.clientname and c.y = '2008'
and c.doy = 2 and c.dow = 'Tuesday')
join ....
The reason this gets ugly is that we're pivoting rows into columns.
这变得丑陋的原因是我们将行转换为列。
回答by Jonathan Leffler
Let's see:
让我们来看看：
SELECT Client, MonSum, TueSum, WedSum, ThuSum, FriSum, SatSum, SunSum, TotSum
FROM (SELECT ClientName AS Client, SUM(Quantity) AS MonSum
FROM Trades
WHERE DayOfWeek(TradeDate) = 'Monday'
AND TradeDate BETWEEN DATE '..Monday..' AND DATE '..Sunday..'
GROUP BY ClientName
) AS MonData
JOIN
(SELECT ClientName AS Client, SUM(Quantity) AS TueSum ...
) AS TueData ON Mondata.Client = TueData.Client
JOIN
...
(SELECT ClientName AS Client, SUM(Quantity) AS TotSum
FROM Trades
WHERE TradeDate BETWEEN DATE '..Monday..' AND DATE '..Sunday..'
GROUP BY ClientName
) AS TotData ON MonData.Client = TotData.Client
ORDER BY Client;
Not tidy, but as @tpdi mentioned in his answer, that's because we're pivoting rows into columns. I've used the consistent TradeDate BETWEEN ...clause to cover the relevant week.
不整洁，但正如@tpdi 在他的回答中提到的那样，那是因为我们将行转为列。我使用了一致的TradeDate BETWEEN ...条款来涵盖相关的一周。
回答by Learning
Thanks for your answers. Actually, I found something in plsql which works out nicely :
感谢您的回答。实际上，我在 plsql 中发现了一些效果很好的东西：
select clientname,
max(decode(trim(dow),'MONDAY',totalquantity,0)) Mon,
max(decode(trim(dow),'TUESDAY',totalquantity,0)) Tue,
max(decode(trim(dow),'WEDNESDAY',totalquantity,0)) Wed,
max(decode(trim(dow),'THURSDAY',totalquantity,0)) Thu,
max(decode(trim(dow),'FRIDAY',totalquantity,0)) Fri,
(
max(decode(trim(dow),'MONDAY',totalquantity,0)) +
max(decode(trim(dow),'TUESDAY',totalquantity,0)) +
max(decode(trim(dow),'WEDNESDAY',totalquantity,0)) +
max(decode(trim(dow),'THURSDAY',totalquantity,0)) +
max(decode(trim(dow),'FRIDAY',totalquantity,0))
) TOTAL
from
(
select clientname,
to_char(tradedate, 'DAY') as dow,
sum(quantity) as totalquantity
from ClientTrade a
where a.tradedate >= trunc(sysdate-7,'D')
and a.tradedate <= trunc(sysdate-7,'D') + 4
group by c.clientshortname, tradedate
)
group by clientname
回答by WaldiMen
My try on Oracle 11G:
我在 Oracle 11G 上的尝试：
select clientname, nvl(MON,0) MON, nvl(TUE,0) TUE, nvl(WED,0) WED, nvl(THU,0) THU, nvl(FRI,0) FRI, nvl(SAT,0) SAT, nvl(SUN,0) SUN,
nvl(MON,0) + nvl(TUE,0) + nvl(WED,0) + nvl(THU,0) + nvl(FRI,0) +nvl(SAT,0) + nvl(SUN,0) TotalForWeek
from
(
select clientname,to_char(tradedate,'Dy') dw, sum(quantity) quantity from ClientTrade
group by clientname, to_char(tradedate,'Dy')
)
pivot (sum(quantity) FOR dw in ('Mon' as MON,'Tue' as TUE,'Wed' AS WED,'Thu' AS THU,'Fri' AS FRI,'Sat' AS SAT,'Sun' AS SUN) )
