sql - SQLite查询每月的第n天

Question

说，我有下表：

CREATE TABLE Data (
    Id INTEGER PRIMARY KEY,
    Value DECIMAL,
    Date DATE);

由于该应用程序与财务相关，因此用户可以选择哪一天是该月的第一天。例如，如果他每个月的第10天收到薪水，则可以将每月的第一天设置为第10天。

我想创建一个查询，该查询返回用户定义的每月第n天的平均值。例如：

 Date           | Value
 ---------------+------
 10.01.2016     | 10
 11.01.2016     | 15
 10.02.2016     | 20
 11.03.2016     | 10

查询结果应为：

 Day | Average
 ----+--------
 1   | 15
 2   | 12.5

请注意，如果用户将第一天设置为每月的10号，9号，则可能是一个月的28号，29号，30号或31号（取决于我们所讨论的月份）。因此，这并不像从日期中提取天数那样简单。

Answer 1

假设日期值不使用格式dd.mm.yyyy，而是使用supported date formats之一，则可以使用built-in date functions进行计算。

要计算两个日期之间以天为单位的差异，请将其转换为以天为数字的日期格式，即儒略日。

要获得一个月的“基准”日，我们可以使用修饰符：

> SELECT julianday('2001-02-11') -
         julianday('2001-02-11', 'start of month', '+10 days') + 2;
2.0

（之所以需要 +2，是因为我们加到每月的1号，而不是0号，并且从1开始计数，而不是0。）

如果日期在十分之一之前，则计算值将变为零或负值，而我们必须改用前一个月：

> SELECT julianday('2001-02-09') -
         julianday('2001-02-09', 'start of month', '-1 month', '+10 days') + 2;
31.0

将这些结果组合到此表达式中以计算日期 Date的n：

CASE
WHEN julianday(Date) -
     julianday(Date, 'start of month', '+10 days') + 2 > 0
THEN julianday(Date) -
     julianday(Date, 'start of month', '+10 days') + 2
ELSE julianday(Date) -
     julianday(Date, 'start of month', '-1 month', '+10 days') + 2
END

您可以在查询中使用它：

SELECT CASE...END AS Day,
       AVG(Value) AS Average
FROM Data
GROUP BY Day;