时间戳

首页 > 解决方案 > SQL按日期查找条目总和，包括前一个日期

sql - SQL按日期查找条目总和，包括前一个日期

问题描述

这可能是不可能的，但这是我试图转换为 SQL 的请求：对于ACCOUNT_ENTRY表中至少存在一个条目的每个日期，我想找到所有帐户的所有条目的总和（在ACCOUNT表中) 为该日期。如果一个帐户没有该特定日期的条目，我想使用该点之前的下一个最新条目。

请注意，我目前正在 H2 DB 上执行此操作，但将来可能会发生变化，因此我试图远离供应商特定的存储过程。

例子

ACCOUNT桌子

ID ACCOUNT_LABEL
1 帐户 123
2 帐户 456
3 帐号 789

ACCOUNT_ENTRY桌子

ID 帐户ID ENTRY_DATE BOOK_VALUE 市场价值
7 1 2021-05-31 100 110
5 1 2021-05-28 90 100
6 2 2021-05-28 70 80
4 3 2021-05-28 50 60
3 1 2021-05-27 80 90
2 2 2021-05-27 60 70
1 3 2021-05-27 40 50

期望的结果

ENTRY_DATE SUM_OF_BOOK_VAL SUM_OF_MARKET_VAL
2021-05-27 180 210
2021-05-28 210 240
2021-05-31 220 250

结果我能走到这一步

ENTRY_DATE SUM_OF_BOOK_VAL SUM_OF_MARKET_VAL
2021-05-27 180 210
2021-05-28 210 240
2021-05-31 100 110

这在我的应用程序代码中很容易做到，但我希望将计算留给数据库，而不是将所有数据传输到应用程序。我也可以前滚数据，但最终可能会得到大量不必要的数据。

这是我目前使用的查询，但它不处理必须回顾以前的条目的情况。

询问

SELECT
    TO_CHAR(ENTRY_DATE, 'yyyy-mm-dd') AS ENTRYDATE,
    SUM(
        CASE
            WHEN A.JOINT_ACCOUNT = 'TRUE' THEN MARKET_VALUE / 2
            ELSE MARKET_VALUE
        END
    ) AS MARKETVALUE,
    SUM(
        CASE
            WHEN A.JOINT_ACCOUNT = 'TRUE' THEN BOOK_VALUE / 2
            ELSE BOOK_VALUE
        END
    ) AS BOOKVALUE
FROM
    ACCOUNT_ENTRY AE
    INNER JOIN ACCOUNT A ON AE.ACCOUNT_ID = A.ID
    INNER JOIN (
        SELECT
            MAX(ID) AS MAX_ID,
            TO_CHAR(ENTRY_DATE, 'yyyy-mm-dd'),
            AE.ACCOUNT_ID AS AID
        FROM
            ACCOUNT_ENTRY AE
        GROUP BY
            AE.ACCOUNT_ID,
            TO_CHAR(ENTRY_DATE, 'yyyy-mm-dd')
    ) MAX_ENTRIES ON AE.ID = MAX_ENTRIES.MAX_ID
GROUP BY
    TO_CHAR(ENTRY_DATE, 'yyyy-mm-dd')
ORDER BY
    TO_CHAR(ENTRY_DATE, 'yyyy-mm-dd') DESC

这可能是不可能的，但这是我试图转换为 SQL 的请求：对于ACCOUNT_ENTRY表中至少存在一个条目的每个日期，我想找到所有帐户的所有条目的总和（在ACCOUNT表中) 为该日期。如果一个帐户没有该特定日期的条目，我想使用该点之前的下一个最新条目。

请注意，我目前正在 H2 DB 上执行此操作，但将来可能会发生变化，因此我试图远离供应商特定的存储过程。

例子

ACCOUNT桌子

ID ACCOUNT_LABEL
1 帐户 123
2 帐户 456
3 帐号 789

ACCOUNT_ENTRY桌子

ID 帐户ID ENTRY_DATE BOOK_VALUE 市场价值
7 1 2021-05-31 100 110
5 1 2021-05-28 90 100
6 2 2021-05-28 70 80
4 3 2021-05-28 50 60
3 1 2021-05-27 80 90
2 2 2021-05-27 60 70
1 3 2021-05-27 40 50

期望的结果

ENTRY_DATE SUM_OF_BOOK_VAL SUM_OF_MARKET_VAL
2021-05-27 180 210
2021-05-28 210 240
2021-05-31 220 250

结果我能走到这一步

ENTRY_DATE SUM_OF_BOOK_VAL SUM_OF_MARKET_VAL
2021-05-27 180 210
2021-05-28 210 240
2021-05-31 100 110

这在我的应用程序代码中很容易做到，但我希望将计算留给数据库，而不是将所有数据传输到应用程序。我也可以前滚数据，但最终可能会得到大量不必要的数据。

这是我目前使用的查询，但它不处理必须回顾以前的条目的情况。

询问

SELECT
    TO_CHAR(ENTRY_DATE, 'yyyy-mm-dd') AS ENTRYDATE,
    SUM(
        CASE
            WHEN A.JOINT_ACCOUNT = 'TRUE' THEN MARKET_VALUE / 2
            ELSE MARKET_VALUE
        END
    ) AS MARKETVALUE,
    SUM(
        CASE
            WHEN A.JOINT_ACCOUNT = 'TRUE' THEN BOOK_VALUE / 2
            ELSE BOOK_VALUE
        END
    ) AS BOOKVALUE
FROM
    ACCOUNT_ENTRY AE
    INNER JOIN ACCOUNT A ON AE.ACCOUNT_ID = A.ID
    INNER JOIN (
        SELECT
            MAX(ID) AS MAX_ID,
            TO_CHAR(ENTRY_DATE, 'yyyy-mm-dd'),
            AE.ACCOUNT_ID AS AID
        FROM
            ACCOUNT_ENTRY AE
        GROUP BY
            AE.ACCOUNT_ID,
            TO_CHAR(ENTRY_DATE, 'yyyy-mm-dd')
    ) MAX_ENTRIES ON AE.ID = MAX_ENTRIES.MAX_ID
GROUP BY
    TO_CHAR(ENTRY_DATE, 'yyyy-mm-dd')
ORDER BY
    TO_CHAR(ENTRY_DATE, 'yyyy-mm-dd') DESC

找到解决方案

感谢@Gordon Linoff 分享 COALESCE 和 LAG 函数。

首先，为了简化我的查询，我创建了一个视图。这与我面临的挑战无关，但很重要，这样我就可以消除同一帐户和同一天的多个条目

create view LATEST_ACCOUNT_ENTRY as
select
   max(id) as entry_id,
   account_id,
   to_char(entry_date, 'yyyy-mm-dd') as e_date
from
   account_entry
group by
   account_id,
   e_date

最终查询（将被简化）

select
   e_date,
   sum(coalesce(BOOK_VALUE, PREVIOUS_BOOK_VALUE)),
   sum(coalesce(MARKET_VALUE, PREVIOUS_MARKET_VALUE))
from
   (
      select
         dates.e_date,
         acc_id,
         entry_id,
         book_value,
         LAG(book_value) OVER (
            partition by dates.acc_id
            order by
               dates.e_date
         ) as previous_book_value,
         market_value,
         LAG(market_value) OVER (
            partition by dates.acc_id
            order by
               dates.e_date
         ) as previous_market_value
      from
         (
            select
               distinct to_char(entry_date, 'yyyy-mm-dd') as e_date,
               accts.id as acc_id
            from
               account_entry
               cross join (
                  select
                     id
                  from
                     account
               ) accts
         ) dates
         left join (
            select
               lae.ACCOUNT_ID,
               lae.E_DATE,
               lae.ENTRY_ID,
               CASE
                        WHEN JOINT_ACCOUNT = 'TRUE' THEN BOOK_VALUE / 2 
                        ELSE BOOK_VALUE
                    END as BOOK_VALUE,
                    CASE
                        WHEN JOINT_ACCOUNT = 'TRUE' THEN MARKET_VALUE / 2 
                        ELSE MARKET_VALUE
                    END as MARKET_VALUE
            from
               LATEST_ACCOUNT_ENTRY lae
               inner join account_entry ae on lae.ENTRY_ID = ae.id
               inner join account acc on ae.account_id = acc.id
         ) entries on dates.e_date = entries.e_date
         and dates.acc_id = entries.account_id
   )
group by
   e_date

标签： sqlh2

解决方案

您可以通过为所有日期和帐户生成行然后使用lag(ignore nulls)来获取最新值来做到这一点。因此，对于行：

select a.account_id, d.entry_date, ae.book_val, ae.market_val,
       coalesce(ae.book_val,
                lag(ae.book_val) ignore nulls over (partition by a.account_id order by d.entry_date)
               ) as imputed_book_val,
       coalesce(ae.market_val,
                lag(ae.market_val) ignore nulls over (partition by a.account_id order by d.entry_date)
               ) as imputed_market_val
from (select distinct account_id from account_entry) a cross join
     (select distinct entry_date from account_entry) d left join
     account_entry ae
     on ae.account_id = a.account_id and
        ae.entry_date = d.entry_date;

然后你可以聚合这个：

select entry_date, sum(imputed_book_val), sum(imputed_market_val)
from (select a.account_id, d.entry_date, ae.book_val, ae.market_val,
             coalesce(ae.book_val,
                      lag(ae.book_val) ignore nulls over (partition by a.account_id order by d.entry_date)
                     ) as imputed_book_val,
             coalesce(ae.market_val,
                      lag(ae.market_val) ignore nulls over (partition by a.account_id order by d.entry_date)
                     ) as imputed_market_val
      from (select distinct account_id from account_entry) a cross join
           (select distinct entry_date from account_entry) d left join
           account_entry ae
           on ae.account_id = a.account_id and
              ae.entry_date = d.entry_date
     ) ad
group by entry_date;

推荐阅读