WITH RECURSIVE date_range AS (

  SELECT '2023-08-31' AS date

  UNION ALL

  SELECT DATE_ADD(date, INTERVAL 1 DAY)

  FROM date_range

  WHERE DATE_ADD(date, INTERVAL 1 DAY) <= CURDATE()

),

C AS (SELECT date,ods_dim_fund.ITEMID AS id

FROM date_range 

JOIN ods_dim_fund

WHERE ods_dim_fund.TYPE = '子公司' 

order by date),

B AS (

SELECT DATE,ID,现金收入,银行收入,银行支出,现金支出,(现金收入+银行收入-银行支出-现金支出) AS 当日余额,(银行支出+现金支出) AS 当日支出

FROM

(

SELECT DATE,ID,IFNULL(现金收入,0) AS 现金收入,

IFNULL(银行收入,0) AS 银行收入,

IFNULL(银行支出,0) AS 银行支出,

IFNULL(现金支出,0) AS 现金支出

FROM C

LEFT JOIN 日报

ON C.ID = 日报.pitemid AND

C.DATE = 日报.时间

ORDER BY DATE)TT),

A AS (SELECT  

date,

id,

银行支出,

现金支出,

SUM(现金收入) OVER (PARTITION by id order  BY date) AS 累计现金,

SUM(银行收入) OVER (PARTITION by id order  BY date) AS 累计银行,

SUM(当日余额) OVER (PARTITION by id order  BY date) AS 累计余额

FROM B

ORDER BY ID)

SELECT date,

id,累计现金,累计银行,累计余额,

 LAG(累计现金) OVER (PARTITION by id order  BY date) AS 上日现金,

 LAG(累计银行) OVER (PARTITION by id order  BY date) AS 上日银行,

  LAG(累计余额) OVER (PARTITION by id order  BY date) AS 上日余额

 FROM A

 where date = '2023-10-11'

 ORDER BY ID,DATE

累计收入数据是都有的  但是为什么限定日期后lag偏移完以后是没有的

不限定date：