月头月尾oracle取数,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;

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

等等，别走！ ^_^

好消息，快手官方发红包啦，登录即送2元，秒提现！ 查看详情>>