作者cutekid (可爱小孩子)
看板Database
标题Re: [SQL ] 当月数量不足，往前抓一个月。
时间Mon Jul 22 15:01:12 2019
-- 测试资料
select *
into #sample
from (
values
('物料1','铁',25,convert(date,'2018/3/1')),
('物料1','铁',0.1,'2018/3/1'),
('物料1','铁',0.56,'2018/4/11'),
('物料1','铁',10,'2018/5/12'),
('物料1','铁',10,'2018/6/25'),
('物料1','铁',10,'2018/6/30'),
('物料1','铁',10,'2018/7/30')
) t(partNO,PARAMETER_NAME,Numerical,DATE)
-- 当月标准差
;with tb1 as (
select
partNO,PARAMETER_NAME,
ym = convert(char(6),DATE,112),
stdev = stdev(Numerical),
-- 取得前次月份(lag 用法:https://bit.ly/2Oarh5g )
prev_ym = lag(convert(char(6),DATE,112))
over (partition by partNO,PARAMETER_NAME order by convert(char(6),DATE,112))
from #sample
group by partNO,PARAMETER_NAME,convert(char(6),DATE,112)
)
-- 最终结果
select t1.partNO,t1.PARAMETER_NAME,t1.ym,
stdev = isnull(
t1.stdev,
stdev(t2.Numerical))
from tb1 t1 left join #sample t2
on t1.partNO = t2.partNO and t1.PARAMETER_NAME = t2.PARAMETER_NAME
and
t1.stdev is null and
convert(char(6),t2.DATE,112) in (t1.ym,t1.prev_ym)
and
datediff(month,t2.DATE,t1.ym + '01') <= 6
group by t1.partNO,t1.PARAMETER_NAME,t1.ym,t1.stdev
/* 说明:
红色: 原本该月标准差
黄色: 与前次月份合并
蓝色: 与前次月份差距不超过半年
*/
※ 引述《carsun00 (永夜)》之铭言：
: 资料库名称：MS SQL
: 资料库版本：14.0.2014.14
: 内容/问题描述：
: 资料需求是本月的数据算不出结果(标准差)，
: 自动加前一个月的数据，如果都没有继续往前追朔最多半年，
: SQL语法如下，目前只能计算出每个月的资料，
: 想到的解法是资料表内，
: 开一个栏位储存查询的次数(1~6)，
: 在使用While跑查询。
: 但这样会变成会可能须要查询六次，
: 不知道有没有比较好的处理方式。
: SELECT
: [PartNo]
: , [PARAMETER_NAME]
: , STDEV([Numerical])
: , CONVERT(CHAR(7), [DATE], 120) AS '月分组'
: FROM
: [dbo].[SAMPLES_Numerical]
: GROUP BY
: [PartNo]
: , [PARAMETER_NAME]
: , CONVERT(CHAR(7), [DATE], 120);
: 谢谢协助m(_ _)m。
: OS：觉得功力薄弱...
: 有点想去上MSSA的70-761..
: 原始资料
: partNO PARAMETER_NAME Numerical DATE
: 物料1 铁 25 2018/3/1
: 物料1 铁 0.1 2018/3/1
: 物料1 铁 0.56 2018/4/11
: 物料1 铁 10 2018/5/12
: 物料1 铁 10 2018/6/25
: 物料1 铁 10 2018/7/30
: 目前sql查询结果(错误)
: 物料1 铁 0.001685269 3月
: 物料1 铁 null 4月
: 物料1 铁 null 5月
: 物料1 铁 0.001604836 6月
: 期望结果
: 物料1 铁 0.001685269 3月
: 物料1 铁 0.001685269 4月 3月&4月计算结果
: 物料1 铁 0.001685269 5月 4月%5月计算结果
: 物料1 铁 0.001604836 6月 6月
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 122.118.82.76 (台湾)
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Database/M.1563778875.A.2F0.html
※ 编辑: cutekid (122.118.82.76 台湾), 07/22/2019 15:06:20
1F：推 carsun00: 谢谢 学到新用法了 07/23 09:20
2F：→ carsun00: 差点就开表慢慢算了 orz... 07/23 09:20