当前位置:首页 > 个人杂谈 > 正文内容

Oracle分析函数Over()的使用

2019-03-02 | 分类:个人杂谈 浏览:493次

今天为完成客户成品仓盘点的统计功能,做了盘点统计表,这个功能有点复杂,要将生产数据与库存的差异做成一个统计报表,报表要反映出差异的明细。

Oracle分析函数Over()的使用

其实,这个项目拖了也挺久了,本来客户计划用于2018年终盘点,不过,后来管理层作出决定,认为年终盘点启动一个新项目的有风险,所以,项目交期延长,改为了今年的季度盘点,即3月底的盘点,现在已经3月份了,项目要尽快完成,以便客户使用。

为了实际统计报表一目了然的效果,必须显示生产型号、WIP结存、库存数(包括:货架,尾数架及尾数框),重点是显示差异的明细。在做报表过程,查询了很多关于ORACLE的函数,其中对于分析函数Over()还是第一次使用,该函数功能强大,特别适合使用于数据统计方面。

over()函数写法over(partition by expr2 order by expr3),根据expr2对结果进行分区,在各分区内按照expr3进行排序;

over函数不能单独使用,需要与row_number(),rank()和dense_rank,lag()和lead(),sum()等配合使用。

这三个函数与over连用时order by 不能丢失,不写partition by时默认结果集为一整个分区。
row_number()与over(partition exp2 order by exp3)配合使用可以对分区根据exp1进行排序,此排序是从1开始,即使遇到相同的数据,也区分开来排序。

我写的盘点统计SQL如下:

CREATE OR REPLACE VIEW BARCODE_FG_CHECK_STOCK_AL AS
SELECT S.EDITION AS 盘点版本,
S.SOURCE_CPN AS 型号,
S.QTY AS 盘点总数,
NVL(W.QTY, 0) AS WIP数量,
S.QTY – NVL(W.QTY, 0) AS 差异,
S.SHELF_ID AS 货架编号,
S.SHELF_ID_BACKLOG AS 货架数量,
S.MANTISSA_SHELF_ID AS 尾数架编号,
S.MANTISSA_SHELF_ID_BACKLOG AS 尾数架数量,
S.MANTISSA_CABINET_ID AS 尾数柜编号,
S.MANTISSA_CABINET_ID_BACKLOG AS 尾数柜数量
FROM (SELECT A.EDITION,
A.SOURCE_CPN,
A.QTY,
B.SHELF_ID,
B.SHELF_ID_BACKLOG,
B.MANTISSA_SHELF_ID,
B.MANTISSA_SHELF_ID_BACKLOG,
B.MANTISSA_CABINET_ID,
B.MANTISSA_CABINET_ID_BACKLOG
FROM (SELECT V.EDITION,
M.SOURCE_CPN,
SUM(M.SHELF_ID_BACKLOG) +
SUM(M.MANTISSA_SHELF_ID_BACKLOG) +
SUM(M.MANTISSA_CABINET_ID_BACKLOG) AS QTY
FROM BARCODE_FG_INVENTORY M, BARCODE_FG_INVENTORY_VER V
WHERE M.VER_PTR = V.PKEY
GROUP BY V.EDITION, M.SOURCE_CPN) A,
(select BARCODE_FG_CHECK_STOCK_ALL.SOURCE_CPN,
DECODE(row_number()
OVER(partition by SOURCE_CPN,SHELF_ID ORDER BY SOURCE_CPN),
1,
SHELF_ID) SHELF_ID,
DECODE(row_number() OVER(partition by SOURCE_CPN,SHELF_ID_BACKLOG
ORDER BY SOURCE_CPN),
1,
SHELF_ID_BACKLOG) SHELF_ID_BACKLOG,
DECODE(row_number()
OVER(partition by SOURCE_CPN,MANTISSA_SHELF_ID ORDER BY
SOURCE_CPN),
1,
MANTISSA_SHELF_ID) MANTISSA_SHELF_ID,
DECODE(row_number()
OVER(partition by SOURCE_CPN,MANTISSA_SHELF_ID_BACKLOG
ORDER BY SOURCE_CPN),
1,
MANTISSA_SHELF_ID_BACKLOG) MANTISSA_SHELF_ID_BACKLOG,
DECODE(row_number()
OVER(partition by SOURCE_CPN,MANTISSA_CABINET_ID ORDER BY
SOURCE_CPN),
1,
MANTISSA_CABINET_ID) MANTISSA_CABINET_ID,
DECODE(row_number()
OVER(partition by SOURCE_CPN,MANTISSA_CABINET_ID_BACKLOG
ORDER BY SOURCE_CPN),
1,
MANTISSA_CABINET_ID_BACKLOG) MANTISSA_CABINET_ID_BACKLOG
from BARCODE_FG_CHECK_STOCK_ALL) B
WHERE A.SOURCE_CPN = B.SOURCE_CPN) S,
(SELECT CPN, SUM(QTY_BACKLOG_UNIT) AS QTY
FROM (SELECT SUBSTR(DATA0050.CUSTOMER_PART_NUMBER, 1, 7) AS CPN,
DATA0056.QTY_BACKLOG_UNIT
FROM DATA0056, DATA0006, DATA0050, DATA0034
WHERE DATA0056.WO_PTR = DATA0006.PKEY
AND DATA0006.CUST_PART_PTR = DATA0050.PKEY
AND DATA0056.DEPT_PTR = DATA0034.PKEY
AND DATA0056.QTY_BACKLOG > 0
AND DATA0034.DEPT_CODE = ’24′)
GROUP BY CPN) W
WHERE S.SOURCE_CPN = W.CPN(+)
ORDER BY S.EDITION, S.SOURCE_CPN;

当然,这个并非最终版本,虽然功能都已经实际,不过,对于同一型号有多个货架与多个尾数架的数据时,可能会存在统计不准,这个需要再测试,以后完成盘点功能后再发一个牌本吧。

文章由葛一速博客原创,转载请注明出处:http://www.geyisu.com/619.html

  • 评论:(0)

已有 0 位网友发表了一针见血的评论,你还等什么?

◎欢迎大家参与讨论

站内搜索