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;

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