主页 / 问题 / 1146818
Accepted
Majestio
Asked:2020-06-29 13:15:37 +0000 UTC2020-06-29 13:15:37 +0000 UTC

按月优化查询

  • 772

该表有一个字段“活动”，实际上，其中记录了一个时间戳。我需要某一年（比如说 2020 年）按月显示记录数。结果应该是包含 12 个字段的记录。我做了这个查询：

SELECT
  SUM(F1)  AS 'M1',
  SUM(F2)  AS 'M2',
  SUM(F3)  AS 'M3',
  SUM(F4)  AS 'M4',
  SUM(F5)  AS 'M5',
  SUM(F6)  AS 'M6',
  SUM(F7)  AS 'M7',
  SUM(F8)  AS 'M8',
  SUM(F9)  AS 'M9',
  SUM(F10) AS 'M10',
  SUM(F11) AS 'M11',
  SUM(F12) AS 'M12'
FROM (
  SELECT
    CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '01'  THEN COUNT(G.Activity) ELSE 0 END AS 'F1',
    CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '02'  THEN COUNT(G.Activity) ELSE 0 END AS 'F2',
    CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '03'  THEN COUNT(G.Activity) ELSE 0 END AS 'F3',
    CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '04'  THEN COUNT(G.Activity) ELSE 0 END AS 'F4',
    CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '05'  THEN COUNT(G.Activity) ELSE 0 END AS 'F5',
    CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '06'  THEN COUNT(G.Activity) ELSE 0 END AS 'F6',
    CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '07'  THEN COUNT(G.Activity) ELSE 0 END AS 'F7',
    CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '08'  THEN COUNT(G.Activity) ELSE 0 END AS 'F8',
    CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '09'  THEN COUNT(G.Activity) ELSE 0 END AS 'F9',
    CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '10'  THEN COUNT(G.Activity) ELSE 0 END AS 'F10',
    CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '11'  THEN COUNT(G.Activity) ELSE 0 END AS 'F11',
    CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '12'  THEN COUNT(G.Activity) ELSE 0 END AS 'F12'
  FROM Guests AS G
  WHERE strftime('%Y', datetime(G.Activity, 'unixepoch')) = '2020'
  GROUP BY strftime('%m', datetime(G.Activity, 'unixepoch'))
)

请求工作正常。但在我看来，它变得太麻烦了。可以重写/优化吗？

sql

1 个回答

  1. Best Answer

    为什么不只是扩大表格？

    SELECT strftime('%m', datetime(activity, 'unixepoch')) AS month
     , COUNT(activity) AS count
  FROM guests
 GROUP BY strftime('%m', datetime(activity, 'unixepoch'))
;

    答案示例：

    月|计数
   01|1
   02|1
   05|1
   09|2
    • 1

相关问题