sqlite - 德尔福和SQLite : Get the records count per month for the last 12 months

Question

我正在尝试获取过去 12 个月(包括当月)按月分组的记录数。不是从今年年初开始。

我有一个表 EVENTS 和一个字段 WODATE。我能做到

  aFDQuery.Connection := TrackingDBConnection;
  aFDQuery.SQL.Clear;
  with aFDQuery.SQL do
    begin
      BeginUpdate;
      try
        Add('Select MONTH(MAX(WODate)) AS month, COUNT(*) AS count ');
        Add('from events where YEAR(WODATE) = 2020 ');
        Add('GROUP BY YEAR(WODate), MONTH(WODate) ORDER BY YEAR(WODate), MONTH(WODate) ASC');
      finally
        EndUpdate;
      end;

通过该查询，我仅获得值不为 0 的月份的结果，并且显然仅获得 2020 年的结果

但我想获得从现在开始的最后 12 个月，即使是这样的 0

[01]    11/2019     12 
[02]    12/2019     24 
[03]    01/2020      0 
[04]    02/2020     12
[05]    03/2020     44
[06]    04/2020      0
[07]    05/2020     21
[08]    06/2020     37
[09]    07/2020      0
[10]    08/2020     15 
[11]    09/2020     45 
[12]    10/2020      6

我已经尝试过，但出现语法错误

Add('from EVENTS where WODATE >= DATE(NOW, -12 month) ');

ESQLiteNativeException: [FireDAC][Phys][SQLite] ERROR: near "month": syntax error

这可能吗？预先感谢您的任何建议

根据彼得的回答(见下文)，我明白了

2019-11-01 - 2019-11-30    0
2019-12-01 - 2019-12-31    0
2020-01-01 - 2020-01-31    0
2020-02-01 - 2020-02-29    0
2020-03-01 - 2020-03-31    0
2020-04-01 - 2020-04-30    0
2020-05-01 - 2020-05-31    0
2020-06-01 - 2020-06-30    0
2020-07-01 - 2020-07-31    0
2020-08-01 - 2020-08-31    0
2020-09-01 - 2020-09-30    0
2020-10-01 - 2020-10-31   13

哪个是完美的非常感谢彼得。你值得喝一杯比利时啤酒

Answer 1

这并不是一个 Delphi 问题，而是 SQLite 问题。自 version 3.8.3您可以使用common table expressions (CTE) 和 VALUES clause 。下面我将解释如何。

首先，您可以构建包含 12 个条目的静态日期范围表，其中每个条目代表过去 12 个月(包括当前)的一个月:

VALUES
  (date('now', 'start of month', '-11 month'), date('now', 'start of month', '-10 month', '-1 day')),
  (date('now', 'start of month', '-10 month'), date('now', 'start of month', '-9 month', '-1 day')),
  (date('now', 'start of month', '-9 month'),  date('now', 'start of month', '-8 month', '-1 day')),
  (date('now', 'start of month', '-8 month'),  date('now', 'start of month', '-7 month', '-1 day')),
  (date('now', 'start of month', '-7 month'),  date('now', 'start of month', '-6 month', '-1 day')),
  (date('now', 'start of month', '-6 month'),  date('now', 'start of month', '-5 month', '-1 day')),
  (date('now', 'start of month', '-5 month'),  date('now', 'start of month', '-4 month', '-1 day')),
  (date('now', 'start of month', '-4 month'),  date('now', 'start of month', '-3 month', '-1 day')),
  (date('now', 'start of month', '-3 month'),  date('now', 'start of month', '-2 month', '-1 day')),
  (date('now', 'start of month', '-2 month'),  date('now', 'start of month', '-1 month', '-1 day')),
  (date('now', 'start of month', '-1 month'),  date('now', 'start of month', '-1 day')),
  (date('now', 'start of month'),              date('now', 'start of month', '+1 month', '-1 day'))

这给出(截至当前日期):

<表类=“s-表”><标题>开始日期结束日期 <正文>2019-11-012019-11-302019-12-012019-12-312020-01-012020-01-312020-02-012020-02-292020-03-012020-03-312020-04-012020-04-302020-05-012020-05-312020-06-012020-06-302020-07-012020-07-312020-08-012020-08-312020-09-012020-09-302020-10-012020-10-31

使用 CTE，您可以加入 events表到上面的列表:

WITH DateRanges(StartDate, EndDate) AS (VALUES
  (date('now', 'start of month', '-11 month'), date('now', 'start of month', '-10 month', '-1 day')),
  (date('now', 'start of month', '-10 month'), date('now', 'start of month', '-9 month', '-1 day')),
  (date('now', 'start of month', '-9 month'),  date('now', 'start of month', '-8 month', '-1 day')),
  (date('now', 'start of month', '-8 month'),  date('now', 'start of month', '-7 month', '-1 day')),
  (date('now', 'start of month', '-7 month'),  date('now', 'start of month', '-6 month', '-1 day')),
  (date('now', 'start of month', '-6 month'),  date('now', 'start of month', '-5 month', '-1 day')),
  (date('now', 'start of month', '-5 month'),  date('now', 'start of month', '-4 month', '-1 day')),
  (date('now', 'start of month', '-4 month'),  date('now', 'start of month', '-3 month', '-1 day')),
  (date('now', 'start of month', '-3 month'),  date('now', 'start of month', '-2 month', '-1 day')),
  (date('now', 'start of month', '-2 month'),  date('now', 'start of month', '-1 month', '-1 day')),
  (date('now', 'start of month', '-1 month'),  date('now', 'start of month', '-1 day')),
  (date('now', 'start of month'),              date('now', 'start of month', '+1 month', '-1 day'))
)
SELECT
  DateRanges.StartDate,
  DateRanges.EndDate,
  COUNT(Events.ROWID) AS Count
FROM DateRanges
  LEFT OUTER JOIN Events ON (DateRanges.StartDate <= Events.WODate) AND (Events.WODate <= DateRanges.EndDate)
GROUP BY
  DateRanges.StartDate, DateRanges.EndDate
ORDER BY
  DateRanges.StartDate

参见sample SQLFiddle .

编辑

我发现您正在努力使用 TFDQuery 执行 SQL ，所以我为你做到了:

with aFDQuery.SQL do
begin
  BeginUpdate;
  try
    Add('WITH DateRanges(StartDate, EndDate) AS (VALUES');
    Add('(date(''now'', ''start of month'', ''-11 month''), date(''now'', ''start of month'', ''-10 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-10 month''), date(''now'', ''start of month'', ''-9 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-9 month''),  date(''now'', ''start of month'', ''-8 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-8 month''),  date(''now'', ''start of month'', ''-7 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-7 month''),  date(''now'', ''start of month'', ''-6 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-6 month''),  date(''now'', ''start of month'', ''-5 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-5 month''),  date(''now'', ''start of month'', ''-4 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-4 month''),  date(''now'', ''start of month'', ''-3 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-3 month''),  date(''now'', ''start of month'', ''-2 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-2 month''),  date(''now'', ''start of month'', ''-1 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-1 month''),  date(''now'', ''start of month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month''),              date(''now'', ''start of month'', ''+1 month'', ''-1 day'')))');
    Add('SELECT DateRanges.StartDate, DateRanges.EndDate, COUNT(Events.ROWID) AS Count');
    Add('FROM DateRanges LEFT OUTER JOIN Events ON (DateRanges.StartDate <= Events.WODate) AND (Events.WODate <= DateRanges.EndDate)');
    Add('GROUP BY DateRanges.StartDate, DateRanges.EndDate');
    Add('ORDER BY DateRanges.StartDate');
  finally
    EndUpdate;
  end;
end;