每个日期的SQL滚动总和-Java 学习之路

-1

我有以下SQL查询，用于根据用户的输入生成交易数据的报告 . 用户选择报告的开始日期和结束日期并生成报告 . 在通常变量的地方，我输入了2018-05-01和2018-05-06的日期以便于参考 .

以下是此查询中使用的表的基础知识以及示例中使用的两行 .

create table activity (
Act_ID int(11),
ACT_QTY int(11),
ACT_PRICE decimal(8,5),
ACT_TRADE_DT date,
ACT_SETTLE_DT date, 
ACT_EXTND_SETT varchar(1),
ACT_HOLD_STATUS varchar(140)
);

INSERT INTO activity (ACT_QTY, ACT_PRICE, ACT_TRADE_DT, ACT_SETTLE_DT, 
ACT_EXTND_SETT )
VALUES ('10', '103.33', '2018-04-25', '2018-05-02', 'Y'), 
('5', '103.40', '2018-04-26', '2018-05-04', 'Y');

这是正在运行的查询 .

SELECT date_query.date_range AS 'Date', 
CASE WHEN a.sum_qty is NULL THEN 0 ELSE a.sum_qty END AS 'QTY Sum', 
CASE WHEN a.Total_Dollar is NULL THEN 0 ELSE a.Total_Dollar END AS 'Total 
Dollar', 
CASE WHEN a.Total_Cap_Cost is NULL THEN 0 ELSE a.Total_Cap_Cost END AS 'Total Capital Used' 
FROM
  (
  select sub.date_range from 
    (select adddate('2001-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) 
date_range from
    (select 0 t0 union select 1 union select 2 union select 3 union select 4 
union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    (select 0 t1 union select 1 union select 2 union select 3 union select 4 
union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    (select 0 t2 union select 1 union select 2 union select 3 union select 4 
union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    (select 0 t3 union select 1 union select 2 union select 3 union select 4 
union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    (select 0 t4 union select 1 union select 2 union select 3 union select 4 
union select 5 union select 6 union select 7 union select 8 union select 9) t4) sub
    where date_range between '2018-05-01' and '2018-05-06'
  ) date_query
LEFT OUTER JOIN
    (
      SELECT ACT_SETTLE_DT, sum( ACT_QTY ) AS 'Sum_QTY', round((ACT_PRICE * 
ACT_QTY * 10), 2) AS Total_Dollar, round((ACT_PRICE * ACT_QTY * 10 * .07),2) 
AS Total_Cap_Cost FROM activity WHERE ACT_EXTND_SETT = 'Y' AND ACT_HOLD_STATUS != 
'Cancelled' GROUP BY ACT_SETTLE_DT
    ) a
ON date_query.date_range = a.act_settle_dt
ORDER BY date_query.date_range ASC

当前输出如下所示：

2018-05-01 | 0   | 0        | 0
2018-05-02 | 10  |10333.00  | 723.31
2018-05-03 | 0   | 0        | 0
2018-05-04 | 5   | 5170.00  | 361.90
2018-05-05 | 0   | 0        | 0
2018-05-06 | 0   | 0        | 0

这就是我在努力的地方 .

当act_settle_dt小于date_query.date_range时，我正在尝试调整当前查询以实现每个列的滚动总和 . 而不是每个日期的sum（act_qty），我需要一些sum（act_qty）的影响，其中act_settle_date <date_query.date_range .

新输出应该如下所示：

2018-05-01 | 15  | 15503.00 | 1085.21
2018-05-02 | 15  | 15503.00 | 1085.21
2018-05-03 | 5   | 5170.00  | 361.90
2018-05-04 | 5   | 5170.00  | 361.90
2018-05-05 | 0   | 0        | 0
2018-05-06 | 0   | 0        | 0

我需要能够将用户选择的时间段暴露给尚未结算的每笔交易的数量和金额 .

服务器在MySQL 5.6上，因此使用类似OVER子句的东西不是一种选择 . 我已经尝试了几件事，比如在总和中加入一个案例条款但无济于事 . 我不是SQL的专家，因为我的拼凑查询可能很明显，任何指导都将不胜感激 . 谢谢

UPDATE: 我已将SQL缩小到以下范围 .

SELECT date_query.date_range AS 'Date', 
CASE WHEN a.sum_qty is NULL THEN 0 ELSE a.sum_qty END AS 'Ext Trade Qty', 
CASE WHEN a.Total_Dollar is NULL THEN 0 ELSE a.Total_Dollar END AS 'Total 
Dollar', 
CASE WHEN a.Total_Cap_Cost is NULL THEN 0 ELSE a.Total_Cap_Cost END AS 'Total 
Capital Used',

SUM（例如a.ACT_SETTLE_DT> date_query.date_range那么a.sum_qty ELSE 0 END）AS'累积外部数量'

FROM
  (
  select sub.date_range from 
    (select adddate('2001-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0)         
date_range from
    (select 0 t0 union select 1 union select 2 union select 3 union select 4     
union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    (select 0 t1 union select 1 union select 2 union select 3 union select 4 
union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 
union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) sub
    where date_range between '2018-05-01' and '2018-05-06'
  ) date_query
LEFT OUTER JOIN
(
  SELECT ACT_TRADE_DT, ACT_SETTLE_DT, sum( ACT_QTY ) AS 'Sum_QTY', 
sum(round((ACT_PRICE * ACT_QTY * 10), 2)) AS Total_Dollar, 
sum(round((ACT_PRICE * ACT_QTY * 10 * .07),2)) AS Total_Cap_Cost 
  FROM activity WHERE ACT_EXTND_SETT = 'Y' AND ACT_TRADE_DT <= '2018-05-06' 
AND ACT_SETTLE_DT > '2018-05-01'
  GROUP BY ACT_SETTLE_DT
) a
ON date_query.date_range = a.act_settle_dt
GROUP BY date_query.date_range
ORDER BY date_query.date_range ASC

有人可以解释为什么被阻止的CASE WHEN没有按预期工作吗？

我试图将结算日期与行的当前日期进行比较 . 我希望得到所有行的总和，直到当前行的日期 . 在此更新的代码中，我为所有行的累积列获取0值 . 任何人都可以解释为什么在进行日期比较时没有拿起值的情况？或者我是以完全错误的方式解决这个问题？

1 回答

经过多次试验和错误后，我放弃了CASE方法 .

SUM(CASE WHEN a.ACT_SETTLE_DT > date_query.date_range THEN a.sum_qty ELSE 0 END ) AS 'Cumulative Ext Qty'

我终于能够使用子查询来比较WHERE子句中的日期 . 看起来很简单，我认为它会 . 如果有人有更清洁/更简单的选择，请随时发布 .

(SELECT SUM(ACT_QTY)
    FROM activity A2
    where A2.ACT_TRADE_DT <= date_query.date_range and
          A2.ACT_SETTLE_DT >= date_query.date_range
   ) as 'Cumulative Sum',
(SELECT SUM(round((ACT_PRICE * ACT_QTY * 10), 2))
    FROM activity A2
    where A2.ACT_TRADE_DT <= date_query.date_range and
          A2.ACT_SETTLE_DT >= date_query.date_range
   ) as 'Cumulative Total Dollar',
(SELECT SUM(round((ACT_PRICE * ACT_QTY * 10 * .07),2))
    FROM activity A2
    where A2.ACT_TRADE_DT <= date_query.date_range and
          A2.ACT_SETTLE_DT >= date_query.date_range
   ) as 'Cumululative Cap Total'

结果集按预期显示 .

2018-05-01 | 15  | 15503.00 | 1085.21
2018-05-02 | 15  | 15503.00 | 1085.21
2018-05-03 | 5   | 5170.00  | 361.90
2018-05-04 | 5   | 5170.00  | 361.90
2018-05-05 | 0   | 0        | 0
2018-05-06 | 0   | 0        | 0

回复于 2024-04-19T22:12:05+08:00

每个日期的SQL滚动总和

1 回答

相关问题