sqHow如何根据两个日期获得每周总和值?
我的表值一样......sqHow如何根据两个日期获得每周总和值?
Date Amt Cash Money Name
15-Jun 100 10 20 GUL
16-Jun 200 20 40 ABC
20-Jun 300 30 60 GUL
25-Jun 400 40 80 BCA
28-Jun 500 50 10 GUL
3-Jul 600 60 120 ABC
19-Jun 700 70 140 BCA
26-Jun 800 80 160 ABC
7-Jul 900 90 180 GUL
9-Jul 1000 100 200 ABC
我需要在Oracle。我的两个日期之间的返回值的每周基总和预期的输出。
Date Amt Cash Mony
13 to 19 June 1000 100 200
20 to 26 June 1500 150 300
27 to3 July 1100 110 130
4 to 10 July 1900 190 380
下面的查询在第一个分解子查询中有输入日期(从和到)。这些可以被制作成绑定变量,或者你想用来将这些输入传递给查询的任何机制。然后我在第二个分解子查询中有测试数据;你最终的解决方案中并不需要这些。我在“周”因子查询中创建了所有需要的星期,并使用左外连接,因此没有事务的周将显示0个和。请注意,在主查询中,在我执行连接的情况下,基表中的“日期”列未包含在任何类型的函数中;这允许在该列上使用索引,如果该表非常大,您应该拥有这个索引,或者如果性能可能成为其他任何原因的担忧。请注意,输出与您的不同(缺少最后一行),因为我在表格中的最后一行之前输入to-date
。这是故意的,我想确保查询正常工作。另外:我没有使用“日期”或“星期”作为列名;这是一个非常糟糕的做法。保留的Oracle关键字不应该用作列名。我用“dt”和“wk”代替。
with
user_inputs (from_dt, to_dt) as (
select to_date('4-Jun-2016', 'dd-Mon-yyyy'), to_date('3-Jul-2016', 'dd-Mon-yyyy') from dual
),
test_data (dt, amt, cash, money, name) as (
select to_date('15-Jun-2016', 'dd-Mon-yyyy'), 100, 10, 20, 'GUL' from dual union all
select to_date('16-Jun-2016', 'dd-Mon-yyyy'), 200, 20, 40, 'ABC' from dual union all
select to_date('20-Jun-2016', 'dd-Mon-yyyy'), 300, 30, 60, 'GUL' from dual union all
select to_date('25-Jun-2016', 'dd-Mon-yyyy'), 400, 40, 80, 'BCA' from dual union all
select to_date('28-Jun-2016', 'dd-Mon-yyyy'), 500, 50, 10, 'GUL' from dual union all
select to_date('3-Jul-2016', 'dd-Mon-yyyy'), 600, 60, 120, 'ABC' from dual union all
select to_date('19-Jun-2016', 'dd-Mon-yyyy'), 700, 70, 140, 'BCA' from dual union all
select to_date('26-Jun-2016', 'dd-Mon-yyyy'), 800, 80, 160, 'ABC' from dual union all
select to_date('7-Jul-2016', 'dd-Mon-yyyy'), 900, 90, 180, 'GUL' from dual union all
select to_date('9-Jul-2016', 'dd-Mon-yyyy'), 1000, 100, 200, 'ABC' from dual
),
weeks (start_dt) as (
select trunc(from_dt, 'iw') + 7 * (level - 1)
from user_inputs
connect by level <= 1 + (to_dt - trunc(from_dt, 'iw'))/7
)
select to_char(w.start_dt, 'dd-Mon-yyyy') || ' - ' ||
to_char(w.start_dt + 6, 'dd-Mon-yyyy') as wk,
nvl(sum(t.amt), 0) as tot_amt, nvl(sum(t.cash), 0) as tot_cash,
nvl(sum(t.money), 0) as tot_money
from weeks w left outer join test_data t
on t.dt >= w.start_dt and t.dt < w.start_dt + 7
group by start_dt
order by start_dt
;
输出:
WK TOT_AMT TOT_CASH TOT_MONEY
-------------------------------------------- ---------- ---------- ----------
30-May-2016 - 05-Jun-2016 0 0 0
06-Jun-2016 - 12-Jun-2016 0 0 0
13-Jun-2016 - 19-Jun-2016 1000 100 200
20-Jun-2016 - 26-Jun-2016 1500 150 300
27-Jun-2016 - 03-Jul-2016 1100 110 130
您可以通过一个case语句实现这一目标:
例如
-- test data
with data(dat,
val1,
val2) as
(select sysdate - 7, 12, 13
from dual
union all
select sysdate - 6, 32, 1
from dual
union all
select sysdate - 5, 52, 53
from dual
union all
select sysdate - 4, 2, 16
from dual
union all
select sysdate - 3, 72, 154
from dual)
select -- build up your groups
case
when d.dat < to_date('28.09.2016', 'DD.MM.YYYY') then
'<28.09.'
when d.dat > to_date('30.09.2016', 'DD.MM.YYYY') then
'>30.09.'
else
'28.-30.'
end as grp,
sum(val1),
sum(val2)
from data d
group by case
when d.dat < to_date('28.09.2016', 'DD.MM.YYYY') then
'<28.09.'
when d.dat > to_date('30.09.2016', 'DD.MM.YYYY') then
'>30.09.'
else
'28.-30.'
end;
-- output
grp sum(val1) sum(val2)
28.-30. 84 54
<28.09. 12 13
>30.09. 74 170
要通过组日历星期使用
-- test data
with data(dat,
val1,
val2) as
(select sysdate - 9, 12, 13
from dual
union all
select sysdate - 6, 32, 1
from dual
union all
select sysdate - 5, 52, 53
from dual
union all
select sysdate - 4, 2, 16
from dual
union all
select sysdate + 3, 72, 154
from dual)
select TRUNC(dat, 'iw') ||'-'|| TRUNC(dat+7, 'iw'),
sum(val1),
sum(val2)
from data
group by TRUNC(dat, 'iw') ||'-'|| TRUNC(dat+7, 'iw');
@弗兰克感谢您的答复。两个日期的意思是假设我在'02 -Jan-2016'和'30 -Dec-2016'之间给出它应该返回所有星期一到星期日的总和值 – Velu
在这种情况下,简单地按照TRUNC(a_date,'iw')|| ' - ' || TRUNC(a_date + 7,'iw')' –
你可以试试下面的一样,我选择了13军-2016为起始日期。您可以根据您的要求选择任意日期范围。
with t as
(select dt,
min(dt) over (partition by week)||' to '|| max(dt) over (partition by week) week
from (
select to_date('13-Jun-2016','dd-Mon-yyyy')+(level-1) dt,
ceil(level/7) week
from dual
connect by level<=52))
select week,
sum(amt),
sum(cash),
sum(money)
from (
select your_table.*,
t.week
from your_table,t
where trunc(to_date(your_table.dt,'dd-Mon-yyyy'))=trunc(t.dt))
group by week;
@Mathuguy谢谢你的工作。 – Velu