SQL分析函数处理Excel数据复杂业务

SQL分析函数处理Excel数据复杂业务

1.业务需求

有两个Excel表格数据，A表存储商品的历史采购进货信息，大约3万多条记录，包含利用商品长代码唯一标识商品，大约300多条记录。B表存储当前时间的商品库存数量等信息。

B表：

业务场景和需求：
一个商场对采购的商品代码，名称，商品数量，规格等进行记录，按天存放于采购进货A表，即每天的商品进货明细表。商品每天都有进货和卖出，商品的当前时间（统计时间时）的库存信息存放到B表，即商品库存表。
需求：根据商品的库存数量列出当前的库存商品的进货信息。也就是根据B表的商品的库存数量在A表中找到这些商品当时的进货信息。
这里有个假设：先进的商品先卖出，后进的商品后卖出。即按照进货时间进行先后卖出。换句话说，当前库存的商品肯定是最后一次，或者最后一次和最后第二次或者最后一次，或者最后一次和最后第二次和最后第三次。。。。（以此类推）采购入库的。
实质就是确定商品库存是从那天开始形成的，确定一个时间节点。
解决思路：如果将所有数据按照商品进行分组，每个商品组中按照时间（天）先后顺序排列数据，应该从最后一天从后往前累加每次采购数直到该累加数等于该商品的库存数即可，看该条记录是哪天，然后列出从此天开始到最后一天的商品进货记录数据即可达到需求。
此处有个问题，可能得不到累加数等于商品库存数，如某商品一天进货数比较多，比如2020.12.1统计时牛奶库存400件，2019.10.1 进货100件，卖出60件，剩余40件转为了库存。当从后往前累加到2019.10.2得到360件，400-360=40件，那40件必是2019.10.1进货后转库存的。但是我们A表只有2019.10.1 进货100件的信息，没有区分销售多少，库存多少。所以2019.10.2 是累加到360，2019.10.1号是360+100=460>400. 得不到正好是400件的那天。此时要在这个时间边界上进行判断。情况一：等于累加和，取对应进货日期。情况二：得不到等于累加和的那天进货日期。要确定相邻的两天，后一天小于累加和，前一天大于累加和，然后取后者日期。

通过Excel内置函数等操作，较难处理。通过观察发现两个Excel表的数据符合关系型数据库，即A表的商品长代码和B表的商品长代码能关联上，结合业务需求，可考虑使用Oracle数据库的SQL的分析函数来解决。
具体方法步骤：
1.

2.利用测试数据验证SQL业务逻辑

先用测试数据解释业务逻辑：

B表：
C表：

分别按照A、B表创建对应的数据库表。建表语句：

-- Create table
create table ITEMS_DATA
(
  fee_date     DATE,
  receipt_type VARCHAR2(200),
  receipt_no   VARCHAR2(2000),
  item_code    VARCHAR2(2000),
  item_size    VARCHAR2(2000),
  unit_cal     VARCHAR2(10),
  buy_num      NUMBER(8),
  unit_price   NUMBER(10,2),
  total_cost   NUMBER(15,2)
)
tablespace BIDATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );



-- Create table
create table ITEMS_USED
(
  id        NUMBER(8),
  item_code VARCHAR2(2000),
  item_size VARCHAR2(2000),
  unsed_num NUMBER(10)
)
tablespace BIDATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

表items_data数据：

表items_used数据：

select * from 
( select a.* from (
          select * from (
          select u.*,
                 row_number() over(partition by u.item_code order by u.cal_sum) seq from (
          select * from (
          select t.*,
                sum(buy_num) over(partition by t.item_code order by t.fee_date desc ) cal_sum  from 
          (select a.*,b.unsed_num from items_data a,
                        items_used b
           where a.item_code=b.item_code ) t  
           order by t.item_code
                   )m
          where m.unsed_num<=m.cal_sum
                        ) u     
                             ) p
           where p.seq=1
         )  new_items,
            items_data a
 where new_items.item_code=a.item_code 
 and new_items.fee_date<=a.fee_date 
        ) tt

处理后结果：

可以看出花生油是从2019.12.6号开始转库存的。符合上述业务需求说明。

3.使用正式数据运行

清空测试数据，将Excel数据分别复制到items_data和items_used表。

表items_data数据：
表items_used_new数据：

脚本：

select a.fee_date 入库时间,
       a.ITEM_CODE 商品长代码,
       a.item_size  规格型号,
       new_items.unsed_num 期末结存数量, 
       a.receipt_no 发票号,
        a.buy_num  此次入库数量,
       a.unit_price 单价, 
       a.total_cost 总价
       from (
          select * from (
          select u.*,
                 row_number() over(partition by u.item_code order by u.cal_sum) seq from (
          select * from (
          select t.*,
                sum(buy_num) over(partition by t.item_code order by t.fee_date desc ) cal_sum  from
          (select a.*,b.unsed_num from items_data_new a,
                        items_used_new b
           where a.item_code=b.item_code ) t
           order by t.item_code
                   )m
          where m.unsed_num<=m.cal_sum
                        ) u
                             ) p
           where p.seq=1
         )  new_items,
            items_data_new a
 where new_items.item_code=a.item_code
 and new_items.fee_date<=a.fee_date

输出结果：

4.数据库表一列多行数据转一行多列

试了几个行转列的方法，不太好用，有待研究。所以尝试将一列多行数据转一行一列数据，多个字段拼接到一个单元格中，然后使用Excel中的内置函数进行$符合拆分单元格。

with cc as(
select a.fee_date 入库时间,
       a.ITEM_CODE 商品长代码,
       a.item_size  规格型号,
       new_items.unsed_num 期末结存数量, 
       a.receipt_no 发票号,
        a.buy_num  此次入库数量,
       a.unit_price 单价, 
       a.total_cost 总价
       from (
          select * from (
          select u.*,
                 row_number() over(partition by u.item_code order by u.cal_sum) seq from (
          select * from (
          select t.*,
                sum(buy_num) over(partition by t.item_code order by t.fee_date desc ) cal_sum  from
          (select a.*,b.unsed_num from items_data a,
                        items_used b
           where a.item_code=b.item_code ) t
           order by t.item_code
                   )m
          where m.unsed_num<=m.cal_sum
                        ) u
                             ) p
           where p.seq=1
         )  new_items,
            items_data a
 where new_items.item_code=a.item_code
 and new_items.fee_date<=a.fee_date
)


 select 商品长代码, 
 listagg('规格型号$'||规格型号 ||'$期末结存数量$'||期末结存数量||'$采购日期$'||to_char(入库时间,'yyyy-mm-dd')||'$此次采购数目$'||此次入库数量||'$此次采购金额$'||总价||'$此次采购发票号$'||发票号, '$') 
within group (order by 入库时间) 
from cc
  group by 商品长代码;

输出结果：