查看: 2876|回复: 4

相邻的两行数据求差值。请教有什么好方法

[复制链接]
论坛徽章:
9
2013年新春福章
日期:2013-02-25 14:51:24灰彻蛋
日期:2013-02-28 17:57:18蜘蛛蛋
日期:2013-03-06 17:16:15茶鸡蛋
日期:2013-03-26 17:32:46雪佛兰
日期:2013-11-26 10:47:53优秀写手
日期:2014-01-22 06:00:12马上有车
日期:2014-04-16 17:19:19慢羊羊
日期:2015-03-04 14:53:332015年新春福章
日期:2015-03-06 11:58:39
发表于 2017-9-13 11:47 | 显示全部楼层 |阅读模式


脚本:
create table TEST
(
  P_DATE   DATE not null,  --日期
  P_ID     NUMBER not null, -- 产品ID
  CLERK_ID NUMBER not null, --销售员ID
  SALE_CNT NUMBER,      --销售数量
  SALE_AMT NUMBER  ---销售金额
);

insert into test (P_DATE, P_ID, CLERK_ID, SALE_CNT, SALE_AMT) values (to_date('11-09-2017', 'dd-mm-yyyy'), 246784, 1, 1, 4);
insert into test (P_DATE, P_ID, CLERK_ID, SALE_CNT, SALE_AMT) values (to_date('11-09-2017', 'dd-mm-yyyy'), 246784, 2, 1, 4);
insert into test (P_DATE, P_ID, CLERK_ID, SALE_CNT, SALE_AMT) values (to_date('12-09-2017', 'dd-mm-yyyy'), 246784, 1, 8, 6);
insert into test (P_DATE, P_ID, CLERK_ID, SALE_CNT, SALE_AMT) values (to_date('12-09-2017', 'dd-mm-yyyy'), 246784, 2, 5, 9);
insert into test (P_DATE, P_ID, CLERK_ID, SALE_CNT, SALE_AMT) values (to_date('12-09-2017', 'dd-mm-yyyy'), 246784, 3, 9, 12);


求一个产品下,销售员比前一天的销量、金额的差值,如果没有前一天,取当天的值。 有什么好的实现方法。我这个方法感觉有点笨。
QQ截图20170913114607.png

SELECT p_date 日期, p_id 产品id, clerk_id 销售员id, amt 金额差, cnt 销量差
  FROM (SELECT a.p_date,
               a.p_id,
               a.clerk_id,
               a.sale_amt -
               nvl(lag(a.sale_amt, 1)
                   over(PARTITION BY p_id, clerk_id ORDER BY p_date),
                   0) amt,

               a.sale_cnt -
               nvl(lag(a.sale_cnt, 1)
                   over(PARTITION BY p_id, clerk_id ORDER BY p_date),
                   0) cnt,

               nvl(lead(a.sale_amt, 1)
                   over(PARTITION BY p_id, clerk_id ORDER BY a.p_date),
                   0) xia,
               nvl(lag(a.sale_amt, 1)
                   over(PARTITION BY p_id, clerk_id ORDER BY p_date),
                   0) shang
          FROM test a)
WHERE (shang <> 0 AND xia = 0)
    OR (shang = 0 AND xia = 0);


论坛徽章:
17
优秀写手
日期:2014-02-27 06:00:13秀才
日期:2017-08-11 15:37:32弗兰奇
日期:2017-07-04 09:16:01秀才
日期:2017-06-29 10:16:48乌索普
日期:2017-05-26 08:58:24娜美
日期:2017-05-18 16:07:23ITPUB15周年纪念
日期:2017-05-02 15:22:36妮可·罗宾
日期:2017-04-06 10:06:19处女座
日期:2016-03-10 09:03:26白羊座
日期:2015-10-10 15:01:39
发表于 2017-9-13 14:36 | 显示全部楼层
这样?
SELECT P_DATE 日期, P_ID 产品ID, CLERK_ID 销售员ID, AMT 金额差, CNT 销量差
FROM   (SELECT T.P_DATE, T.P_ID, T.CLERK_ID,
                T.SALE_CNT -
                 NVL(LAG(T.SALE_CNT, 1) OVER(PARTITION BY P_ID, CLERK_ID ORDER BY P_DATE), 0) AS CNT,
                T.SALE_AMT -
                 NVL(LAG(T.SALE_AMT, 1) OVER(PARTITION BY P_ID, CLERK_ID ORDER BY P_DATE), 0) AS AMT,
                ROW_NUMBER() OVER(PARTITION BY P_ID, CLERK_ID ORDER BY P_DATE DESC) RN
         FROM   TEST T)
WHERE  RN = 1

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2017-9-13 16:40 | 显示全部楼层




需求说的不是非常清晰, 如果数据是如下的, 我猜测结果是如下的, 如果我猜的不对, 详细说明

SQL> select * from test1 order by 3, 1;

P_DATE            P_ID   CLERK_ID   SALE_CNT   SALE_AMT
----------- ---------- ---------- ---------- ----------
2017/9/11       246784          1          1          4
2017/9/12       246784          1          8          6
2017/9/11       246784          2          1          4
2017/9/12       246784          2          5          9
2017/9/12       246784          3          9         12
2017/9/11       246784          4          1          4
2017/9/12       246784          4          5          7
2017/9/13       246784          4          7         10
2017/9/16       246784          4          9         12
2017/9/18       246784          4         11         15
2017/9/20       246784          4         13         19
2017/9/21       246784          4         17         20

12 rows selected

SQL>
SQL> select  p_date "日期",
  2          p_id "产品ID",
  3          clerk_id "销售员ID",
  4          diff_sale_amt "金额差",
  5          diff_sale_cnt "销量差"
  6  from
  7  (select p_date,
  8          p_id,
  9          clerk_id,
10          sale_cnt,
11          sale_amt,
12          sale_amt - lag(sale_amt, 1, 0) over(partition by clerk_id, flag order by p_date) diff_sale_amt,
13          sale_cnt - lag(sale_cnt, 1, 0) over(partition by clerk_id, flag order by p_date) diff_sale_cnt,
14          row_number() over(partition by clerk_id, flag order by p_date desc) rn
15  from
16  (select p_date,
17          p_id,
18          clerk_id,
19          sale_cnt,
20          sale_amt,
21          max(flag) over(partition by clerk_id order by p_date) flag
22  from
23  (select p_date,
24          p_id,
25          clerk_id,
26          sale_cnt,
27          sale_amt,
28          case
29           when p_date - lag(p_date) over(partition by clerk_id order by p_date) = 1 then
30                0
31           else
32                row_number() over(partition by clerk_id order by p_date)
33          end flag
34     from test1)))
35  where rn<= 1;

日期            产品ID   销售员ID     金额差     销量差
----------- ---------- ---------- ---------- ----------
2017/9/12       246784          1          2          7
2017/9/12       246784          2          5          4
2017/9/12       246784          3         12          9
2017/9/13       246784          4          3          2
2017/9/16       246784          4         12          9
2017/9/18       246784          4         15         11
2017/9/21       246784          4          1          4

7 rows selected

SQL>



使用道具 举报

回复
论坛徽章:
9
2013年新春福章
日期:2013-02-25 14:51:24灰彻蛋
日期:2013-02-28 17:57:18蜘蛛蛋
日期:2013-03-06 17:16:15茶鸡蛋
日期:2013-03-26 17:32:46雪佛兰
日期:2013-11-26 10:47:53优秀写手
日期:2014-01-22 06:00:12马上有车
日期:2014-04-16 17:19:19慢羊羊
日期:2015-03-04 14:53:332015年新春福章
日期:2015-03-06 11:58:39
 楼主| 发表于 2017-9-14 09:40 | 显示全部楼层
bell6248 发表于 2017-9-13 16:40
需求说的不是非常清晰, 如果数据是如下的, 我猜测结果是如下的, 如果我猜的不对, 详细说明
...

我没说清楚,只对两天的数据做比较,比如今天比较昨天和前天的数据。每天定时触发比较。

使用道具 举报

回复
论坛徽章:
9
2013年新春福章
日期:2013-02-25 14:51:24灰彻蛋
日期:2013-02-28 17:57:18蜘蛛蛋
日期:2013-03-06 17:16:15茶鸡蛋
日期:2013-03-26 17:32:46雪佛兰
日期:2013-11-26 10:47:53优秀写手
日期:2014-01-22 06:00:12马上有车
日期:2014-04-16 17:19:19慢羊羊
日期:2015-03-04 14:53:332015年新春福章
日期:2015-03-06 11:58:39
 楼主| 发表于 2017-9-14 09:42 | 显示全部楼层
chengccy2010 发表于 2017-9-13 14:36
这样?
SELECT P_DATE 日期, P_ID 产品ID, CLERK_ID 销售员ID, AMT 金额差, CNT 销量差
FROM   (SELECT T. ...

,简化不少,学习了

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表