sqlite 使用会计类型年度获取MAX(值)，即：2016/2017年等

uubf1zoe 于 2023-01-21 发布在 SQLite

关注(0)|答案(3)|浏览(152)

我的日历年从07 - 01-（一年的）到06 - 30-（下一年的）。
我的SQLITE DB有一个Timestamp列，它的数据类型是datetime，并将时间戳存储为2023 - 09 - 01 00：00：00。
我想做的是在我的财政年度中获得数据库中所有年份的最新降雪日期。
我已经尝试了下面这个查询的许多变体。这个查询说它运行时没有错误，并且返回"null"值。我正在使用SQLITE DB Browser来编写和测试这个查询。

SELECT Timestamp, MAX(strftime('%m-%d-%Y', Timestamp)) AS lastDate,
snowDepth AS lastDepth FROM DiaryData
WHERE lastDepth <> 0 BETWEEN strftime('%Y-%m-%d', Timestamp,'start of year', '+7 months')
AND strftime('%Y-%m-%d', Timestamp, 'start of year', '+1 year', '+7 months', '- 1 day')
ORDER BY lastDate LIMIT 1

这是我测试数据库里的内容

Timestamp          snowFalling  snowLaying  snowDepth
2021-11-10 00:00:00     0            0         7.2
2022-09-15 00:00:00     0            0         9.5
2022-12-01 00:00:00     1            0         2.15
2022-10-13 00:00:00     1            0         0.0
2022-05-19 00:00:00     0            0         8.82
2023-01-11 00:00:00     0            0         3.77

如果它运行正常，我应该期望：
| 时间戳|最后日期|最后深度|
| - ------|- ------|- ------|
| 2022年5月19日00时00分|二○二二年五月十九日|八点八二分|
我错过了什么，或者这在SQLITE中是不可能的？任何帮助都将不胜感激。

sqlite

来源：https://stackoverflow.com/questions/75188688/get-the-maxvalue-using-fiscal-type-years-ie-2016-2017-etc

3条答案

按热度按时间

fdbelqdn1#

利用SQLite的空列功能按财政年度进行汇总：

SELECT Timestamp, 
       strftime('%m-%d-%Y', MAX(Timestamp)) AS lastDate, 
       snowDepth AS lastDepth 
FROM DiaryData
WHERE snowDepth <> 0
GROUP BY strftime('%Y', Timestamp, '+6 months');

请参见demo。

赞(0）回复(0）举报 2023-01-21

unftdfkk2#

您可以使用ROW_NUMBER窗口函数来解决这个问题，但需要进行一些细微的调整。为了考虑财政年度，您可以将年份划分为向前滑动6个月的时间戳。这样，类似于[2021 - 01 - 01，2021 - 12 - 31]的范围将改为滑动到[2021 - 06 - 01，2022 - 05 - 31]。

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER(
                  PARTITION BY STRFTIME('%Y', DATE(Timestamp_, '+6 months')) 
                  ORDER     BY Timestamp_ DESC           ) AS rn
    FROM tab
)
SELECT Timestamp_, 
       STRFTIME('%d-%m-%Y', Timestamp_) AS lastDate,
       snowDepth                        AS lastDepth
FROM cte
WHERE rn = 1

检查here演示。

赞(0）回复(0）举报 2023-01-21

l7mqbcuq3#

我会先得到每个记录的季节，然后是相对于记录的季节开始日期的降雪日期，最后是相对于记录的季节开始日期的最大降雪日期：

with
  data as (
    select
      *
    , case
        when cast(strftime('%m', "Timestamp") as int) <= 7
          then strftime('%Y-%m-%d', "Timestamp", 'start of year', '-1 year', '+6 months')
        else strftime('%Y-%m-%d', "Timestamp", 'start of year', '+6 months')
      end as "Season start date"
    from DiaryData

    where 1==1
    and "snowDepth" <> 0.0
  )

, data2 as (
    select
      *
    , julianday("Timestamp") - julianday("Season start date")
      as "Showfall date relative to season start date"
    from data
  )

, data3 as (
    select
      "Timestamp"
    , "snowFalling"
    , "snowLaying"
    , "snowDepth"
    from data2

    group by null
    having max("Showfall date relative to season start date")
  )
select
  *
from data3

demo

赞(0）回复(0）举报 2023-01-21

我来回答

sqlite 使用会计类型年度获取MAX(值)，即：2016/2017年等

3条答案

相关问题

热门标签

最新问答