SQL：全年每14天对数据求和

发布于 2024-11-05 00:47:31 字数 564 浏览 1 评论 0原文

EMP__ID | 工作日期 | 已售商品
<前><代码>1 1/1/2010 10 2 2010 年 1 月 1 日 5 1 2010年2月1日 7 1 2010 年 3 月 1 日 13 2 2010 年 4 月 1 日 6

我想编写一个查询，每隔 14 天（两周）对 “Items_Sold” 进行求和，持续 < strong>2010 年全年

为了更清楚地显示，此查询应返回 (26 行)，即每一行中一年中的两周的数量将会有在特定两周内捕获的total Items_Sold

我正在使用 SQL Navigator (oracle SQL)

非常感谢您的帮助祝您

好运，

原文

EMP__ID |
Work_Date | Items_sold

1         1/1/2010       10

2         1/1/2010        5

1         2/1/2010        7

1         3/1/2010       13

2         4/1/2010        6

I want to write a query that will sum the "Items_Sold" every 14 days (fortnight) for entire year 2010

to make it more clear , this query should return ( 26 rows ) which is the number of fortnights in a year , in each row there will be the total Items_Sold that was captured in that particular fortnight

I'm using SQL Navigator ( oracle SQL )

thank you so much for your help

best wishes,,

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

落日海湾 2024-11-12 00:47:40

这应该会得到 26 行，从 1 月 1 日开始，增量为 14 天。这足以让您开始吗？

select to_date('01-01-2010', 'DD-MM-YYYY') + (level-1)*14
from dual
connect by level <= 26;

This should get you exactly 26 rows, starting with January 1st with 14 day increments. Is that enough to get you started?

select to_date('01-01-2010', 'DD-MM-YYYY') + (level-1)*14
from dual
connect by level <= 26;

回复收藏 0 原文

野の 2024-11-12 00:47:39

一种方法可能是根据日期除以 2 得到周数，加上 0.1，然后四舍五入。这会将值从 52 标准化为 26。唯一的问题是有 53 周，因此您应该测试该情况何时发生。

SELECT Round(Cast(To_char(work_date, 'WW') AS DEC(12, 2)) / 2 + .1) fortnight, 
       SUM(items_sold)  AS total_sold 
FROM   table1 
GROUP  BY Round(Cast(To_char(work_date, 'WW') AS DEC(12, 2)) / 2 + .1)

注意：我现在不在 Oracle 盒子附近，所以我还没有测试过这个

One approach might be to get the week from the date divide by 2, add .1 and then round it. This will normalize the values from 52 to 26. The one problem is that there are 53 weeks, so you should test for when that occurs.

SELECT Round(Cast(To_char(work_date, 'WW') AS DEC(12, 2)) / 2 + .1) fortnight, 
       SUM(items_sold)  AS total_sold 
FROM   table1 
GROUP  BY Round(Cast(To_char(work_date, 'WW') AS DEC(12, 2)) / 2 + .1)

Note: I'm not near an Oracle box right now so I haven't tested this

回复收藏 0 原文

若相惜即相离 2024-11-12 00:47:39

此查询返回 26 行，如下所示。

FORT_START  FORT_END        JULIAN_DATE     SUM(ITEMS_SOLD)
--
01-JAN-10   14-JAN-10       14              15
15-JAN-10   28-JAN-10       28              -
29-JAN-10   11-FEB-10       42              7
12-FEB-10   25-FEB-10       56              -
26-FEB-10   11-MAR-10       70              13
12-MAR-10   25-MAR-10       84              -
26-MAR-10   08-APR-10       98              6
09-APR-10   22-APR-10       112             -
...

它创建了 2010 年的日历（只是一个日期表）。如果没有 26 行可供连接，您就无法真正获得所有 26 行。

with calendar as (
select cal_date
from
(
  select to_date('01 2010','MM YYYY')-1 + level as cal_date
  from dual
  where (to_date('01 2010','MM YYYY')-1+level) <= last_day(to_date('12 2010','MM YYYY'))
  connect by level<=365
)
order by cal_date
)
select cal_date - 13 as fort_start, cal_date as fort_end, 
       to_number(to_char(cal_date, 'DDD')) as julian_date, sum(items_sold)
from calendar
left join table1 on (work_date between cal_date - 13 and cal_date)
where (cal_date between date '2010-01-01' and date '2010-12-31'
       and mod(to_number(to_char(cal_date, 'DDD')), 14) = 0
      ) 
group by cal_date - 13, cal_date, to_number(to_char(cal_date, 'DDD'))
order by cal_date;

This query returns 26 rows that look like this.

FORT_START  FORT_END        JULIAN_DATE     SUM(ITEMS_SOLD)
--
01-JAN-10   14-JAN-10       14              15
15-JAN-10   28-JAN-10       28              -
29-JAN-10   11-FEB-10       42              7
12-FEB-10   25-FEB-10       56              -
26-FEB-10   11-MAR-10       70              13
12-MAR-10   25-MAR-10       84              -
26-MAR-10   08-APR-10       98              6
09-APR-10   22-APR-10       112             -
...

It creates a calendar--just a table of dates--for 2010. You can't really get all 26 rows without having 26 rows to join on.

with calendar as (
select cal_date
from
(
  select to_date('01 2010','MM YYYY')-1 + level as cal_date
  from dual
  where (to_date('01 2010','MM YYYY')-1+level) <= last_day(to_date('12 2010','MM YYYY'))
  connect by level<=365
)
order by cal_date
)
select cal_date - 13 as fort_start, cal_date as fort_end, 
       to_number(to_char(cal_date, 'DDD')) as julian_date, sum(items_sold)
from calendar
left join table1 on (work_date between cal_date - 13 and cal_date)
where (cal_date between date '2010-01-01' and date '2010-12-31'
       and mod(to_number(to_char(cal_date, 'DDD')), 14) = 0
      ) 
group by cal_date - 13, cal_date, to_number(to_char(cal_date, 'DDD'))
order by cal_date;

回复收藏 0 原文

临走之时 2024-11-12 00:47:39

我认为这应该可以做到

SELECT SUM(Items_sold) as num_sold
     , DATE_FORMAT(Emp_Date, '%Y') AS work_year
     , FLOOR((DATE_FORMAT(Work_Date, '%j') + 14) / 14) AS work_fortnight
  FROM thetable AS T 
 GROUP 
    BY DATE_FORMAT(Work_Date, '%Y'), FLOOR((DATE_FORMAT(Work_Date, '%j') + 14) / 14);

，但可能会在除夕夜结束。我想如果有必要的话你可以很容易地手动解释这一点。我就假设这家商店没有营业:)

I think this should do it

SELECT SUM(Items_sold) as num_sold
     , DATE_FORMAT(Emp_Date, '%Y') AS work_year
     , FLOOR((DATE_FORMAT(Work_Date, '%j') + 14) / 14) AS work_fortnight
  FROM thetable AS T 
 GROUP 
    BY DATE_FORMAT(Work_Date, '%Y'), FLOOR((DATE_FORMAT(Work_Date, '%j') + 14) / 14);

It's probably going to lop of new years eve, though. Guess you could easily account for that manually if necessary. I'll just assume this store isn't open then :)

回复收藏 0 原文

~没有更多了~