数据如何拆逐个个数要计算标准差

qcdata.zip

使用者记录数据是打个数的...又想求出标准差，

这样似乎无法用SQL套公式STDEV()直接算出来，还是其实用数据库指令就可以算出来呢?

我是想说把数据展开像右边那样好计算出来，请教大神要怎么展开呢?

FineReport akahime 发布于 2022-12-21 10:47 （编辑于 2022-12-21 12:01）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共2回答

最佳回答

lxy2Lv6中级互助
发布于2022-12-21 14:01（编辑于 2022-12-21 14:41）

啥数据库啊，可以先在数据库拆开来吧

with a as(

select 'A' AS A,3 AS B,1 min_qty,6 as qty

UNION ALL

select 'A' AS A,2 AS B,1,7 as qty

)

select a.a,MIN_QTY,SEQ,a.b

,case when MIN_QTY * rnk < b THEN MIN_QTY ELSE b-MIN_QTY*(rnk-1) END AS NEW_QTY,qty

from (

select a.a,a.b,MIN_QTY,count(MIN_QTY) over ( partition by a ) as SEQ,b.number + 1 as rnk,qty

from a

CROSS join master..spt_values b

where b.number * a.MIN_QTY <a.B and b.Type = 'P'

) a

举报收起评论(7)

akahime(提问者) 用mssql，SQL怎拆啊? user录入时就是简打了，我也希望他们逐个打就可以用公式STDEV()一次解决

2022-12-21 14:06

lxy2 回复 akahime(提问者) mysql?

2022-12-21 14:11

lxy2 回复 akahime(提问者) with a as( select \'A\' AS A,3 AS B,1 min_qty,6 as qty UNION ALL select \'A\' AS A,2 AS B,1,7 as qty ) select a.a,MIN_QTY,SEQ,a.b ,case when MIN_QTY * rnk < b THEN MIN_QTY ELSE b-MIN_QTY*(rnk-1) END AS NEW_QTY,qty from ( select a.a,a.b,MIN_QTY,count(MIN_QTY) over ( partition by a ) as SEQ,b.number + 1 as rnk,qty from a CROSS join master..spt_values b where b.number * a.MIN_QTY

2022-12-21 14:22