表结构 tab
id date_time qty
1 2010-02-03 18:00:00 100
1 2010-02-03 18:00:00 200
2 2010-02-03 18:00:00 300
2 2010-02-03 18:00:00 150
3 2010-02-03 18:00:00
4 2010-02-03 18:00:00
5
6
1
2
3实际业务数据和上面一样,id列是产品编号,date_time列是出库日期,qty列是出库数量
这个表存了1年的交易数据,现在想取出这样的数据,谢谢
1.指定月内每天至少出库一次的id及出库总量
2.指定月内1周至少出库一次的id及出库总量
3.指定月内2周至少出库一次的id及出库总量第一条我的思路是用下面的语句,针对123条要求,请大家给个sql学习下,谢谢
非分析函数,用count(distinct to_char(date_time,'yyyymmdd')) = 28 这样好象也可以
SELECT DISTINCT *
FROM (SELECT id,
dense_rank() over (partition by id order by to_char(date_time,'yyyymmdd') rn,
sum(qty) over(partition by id) qty
FROM tab
WHERE date_time > to_Date('20100201','yyyymmdd')
AND date_time < to_Date('20100228','yyyymmdd'))
WHERE rn = 28
id date_time qty
1 2010-02-03 18:00:00 100
1 2010-02-03 18:00:00 200
2 2010-02-03 18:00:00 300
2 2010-02-03 18:00:00 150
3 2010-02-03 18:00:00
4 2010-02-03 18:00:00
5
6
1
2
3实际业务数据和上面一样,id列是产品编号,date_time列是出库日期,qty列是出库数量
这个表存了1年的交易数据,现在想取出这样的数据,谢谢
1.指定月内每天至少出库一次的id及出库总量
2.指定月内1周至少出库一次的id及出库总量
3.指定月内2周至少出库一次的id及出库总量第一条我的思路是用下面的语句,针对123条要求,请大家给个sql学习下,谢谢
非分析函数,用count(distinct to_char(date_time,'yyyymmdd')) = 28 这样好象也可以
SELECT DISTINCT *
FROM (SELECT id,
dense_rank() over (partition by id order by to_char(date_time,'yyyymmdd') rn,
sum(qty) over(partition by id) qty
FROM tab
WHERE date_time > to_Date('20100201','yyyymmdd')
AND date_time < to_Date('20100228','yyyymmdd'))
WHERE rn = 28
select id,sum(qty)
from tab
where date_time>=:start_date and date_time<:end_date+1
group by id
having count(distinct trunc(date_time))=:end_date-:start_date+1;
select id, sum(qty)
from tab
where date_time >= :start_date
and date_time < :end_date + 1
group by id
having count(distinct trunc(date_time, 'iw')) = (select count(distinct
trunc(:start_date +
rownum - 1,
'iw'))
from dual
connect by rownum <=
:end_date -
:start_date + 1);
找出“指定月内1周至少出库一次的id及出库总量”,先查询出这个月内该id的最大data_time,和最小data_time,如果两者的差值等于或大于7就显示该id。但是总数量是什么呢?是这个id这个月内的总数量还是所有记录的?
其它雷同。
--下面的语句是连续一周,如果连续2周,请将level>6改为level>13
SELECT a.id, SUM(qty) amount
FROM tab a
WHERE date_time>=DATE'2010-02-01' AND date_time<DATE'2010-03-01' AND
a.id IN (SELECT id
FROM (SELECT id,trunc(date_time) date_time,qty,
row_number() over(PARTITION BY id ORDER BY date_time) rn
FROM tab) t
WHERE LEVEL > 6
START WITH rn = 1
CONNECT BY PRIOR date_time = date_time - 1 AND
PRIOR id = id)
GROUP BY a.id
FROM tab a
WHERE date_time>=DATE'2010-02-01' AND date_time<DATE'2010-03-01'
AND a.id IN
(select id
from
( SELECT id,
LAST_VALUE(date_time) over (partition by id order by date_time ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as last_value,
FIRST_VALUE(date_time) over (partition by id order by date_time ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as first_value,
ROW_NUMBER() over (partition by id order by date_time) as rowNo
from tab
)
where rowno <> 1
and last_value - first_value <=7
)
GROUP BY a.id;