时间戳

首页 > 解决方案 > 如何有效地确定一个时期内的收益被抹去的日期？(SQLite)

sql - 如何有效地确定一个时期内的收益被抹去的日期？(SQLite)

问题描述

我有一个 SQLite 事务数据库。每个都有一个日期、一个值和一个相应的时间段。我正在寻找消除一段时间内收益的日期。这是一个示例表。

period      date           value
----------  -------------  --------
1           2000-01-01     16.3
1           2000-01-02     1.5
1           2000-01-03     2.9
1           2000-01-11     32.8
2           2000-01-27     -53.5
2           2000-01-27     17.0
2           2000-01-27     94.4
2           2000-02-02     168.0
2           2000-02-05     24.7
2           2000-02-05     66.0
2           2000-02-08     60.5
2           2000-02-09     80.2
3           2000-02-22     7.3
3           2000-02-22     -510.9
3           2000-02-23     25.3
3           2000-02-25     4.2
3           2000-03-20     3.1

对于期间 1，期间收益的总和为 16.3 + 1.5 + 2.9 + 32.8 = 53.5。2000-01-27 的负值 -53.5 完全抹去了这些收益。理想情况下，我想要一个总结这些相应日期的新专栏。例如

period      date           value     erase_date
----------  -------------  --------  -------------
1           2000-01-01     16.3      2000-01-27
1           2000-01-02     1.5       2000-01-27
1           2000-01-03     2.9       2000-01-27
1           2000-01-11     32.8      2000-01-27
2           2000-01-27     -53.5
2           2000-01-27     17.0      2000-01-27
2           2000-01-27     94.4      2000-02-22
2           2000-02-02     168.0     2000-02-22
2           2000-02-09     80.2      2000-02-22
3           2000-02-22     7.3       2000-02-22
3           2000-02-22     -510.9
3           2000-02-23     25.3      2000-02-22
3           2000-02-25     4.2       2000-02-22
3           2000-03-20     3.1       2000-02-22

我已经能够通过使用CASE来隔离正负值，然后选择SUM按期间列排序的正数和按日期列排序的负数的累积SUM值，最后选择最小值总和的负值的大小大于总和的正值的大小的日期。这对于大型表（几千个条目）来说非常慢。我希望我只是错过了一个对有更多使用 SQL 经验的人来说显而易见的模式。

这是我正在尝试的代码示例

WITH view AS (
    SELECT 
        period, date, value
        SUM(positive) OVER (ORDER BY period) period_positive_sum,
        SUM(negative) OVER (ORDER BY date) daily_negative_sum
    FROM (
        SELECT
            period, date, value
            CASE WHEN value >= 0 THEN value END positive
            CASE WHEN value < 0 THEN value END negative
        FROM table
    )
)
SELECT
    v.period, v.date, v.value,
    (SELECT
        MIN(date)
    FROM view
    WHERE
        -daily_negative_sum >= v.period_positive_sum
    ) erase_date
FROM view AS v

标签： sqlsqlite

解决方案

使用此答案中的自联接来产生累积和。

select
  t1.date,
  t1.value,
  sum(t2.value) as gain
from transactions t1
inner join transactions t2 on t1.date >= t2.date
group by t1.date, t1.value
order by t1.date;

然后进行子选择以查找 <= 0 的下一个日期。

with gains as (
    select t1.date, t1.value, sum(t2.value) as gain
    from transactions t1
    inner join transactions t2 on t1.date >= t2.date
    group by t1.date, t1.value
    order by t1.date
)
select
  g1.*,
  (
      select g2.date
      from gains g2
      where g2.date > g1.date
        and g2.gain <= 0
      order by date asc
      limit 1
  ) as erase_date
from gains g1

推荐阅读