sql - 一个 SQLite 查询中的两个总和和三个表

Question

我有三个表:事件、 Action (每个 Action 都是一个事件的执行)和照片(每个 Action 都可以附上照片)。

这里是 an SQL Fiddle for this .

现在我想按降序检索事件，并且对于每个事件，我想要花费在它上面的总时间和附加到它的总照片。使用最后一个 Action 的停止时间计算的事件顺序。

例如，对于以下数据

activities
------------------
 _id |   title
------------------
   1 | Activity 1
   2 | Activity 2
   3 | Activity 3
   4 | Activity 4

actions
-------------------------------------------------------------
_id | activity_id |    date_started     |     date_stopped
-------------------------------------------------------------
  1 |           1 | 2014-01-23 20:45:03 | 2014-01-23 20:45:24
  2 |           2 | 2014-01-23 20:45:27 | 2014-01-23 20:45:29
  3 |           3 | 2014-01-23 20:45:31 | 2014-01-23 20:45:43
  4 |           1 | 2014-01-23 20:45:46 | 2014-01-23 20:45:48
  5 |           4 | 2014-01-23 20:45:50 | 2014-01-23 20:46:19

photos
--------------------------------------------------------
_id | action_id |      date_taken     |     path
--------------------------------------------------------
  1 |         1 | 2014-01-23 20:45:11 | 758712034.jpg
  2 |         1 | 2014-01-23 20:45:21 | 537444469.jpg
  3 |         3 | 2014-01-23 20:45:39 | 28884579.jpg
  4 |         5 | 2014-01-23 20:45:58 | 1519722792.jpg
  5 |         5 | 2014-01-23 20:46:08 | 298808374.jpg
  6 |         5 | 2014-01-23 20:46:15 | 2059925529.jpg

我希望通过此查询获得所需的数据:

SELECT
    activityId, title, sum(seconds) AS totalSeconds, sum(cnt) AS totalPhotos 
FROM
    (
        SELECT
            activities._id AS activityId, activities.title AS title,
            actions._id AS actionId,
            strftime("%s", ifnull(actions.date_stopped, 'now')) -
            strftime("%s", actions.date_started) AS seconds,
            count(photos._id) AS cnt
        FROM
            activities JOIN actions ON activities._id = actions.activity_id
            LEFT OUTER JOIN photos ON photos.action_id = actions._id
        GROUP BY 1,2,3,4
        ORDER BY actionId DESC
    )
GROUP BY 1

但是，不幸的是，它给出了这样的结果:

activityId |   title    | totalSeconds | totalPhotos 
--------------------------------------------------------
         1 | Activity 1 |           23 |           2
         2 | Activity 2 |            2 |           0
         3 | Activity 3 |           12 |           1
         4 | Activity 4 |           29 |           3

我正在尝试得到这个(参见操作表中 activity_id 的顺序):

activityId |   title    | totalSeconds | totalPhotos 
--------------------------------------------------------
         4 | Activity 4 |           29 |           3
         1 | Activity 1 |           23 |           2
         3 | Activity 3 |           12 |           1             
         2 | Activity 2 |            2 |           0

如何更改我的查询以获得我想要的？

Answer 1

( 感谢 设置 SQL Fiddle。这使事情变得更容易)。

您正朝着正确的方向前进 - 您可能需要添加的只是 ORDER BY totalSeconds DESC到您的查询结束。但是，您的查询有几个问题，并且可能会比这些方面更好:

SELECT Activities._id, Activities.title, Actions.totalSeconds, Actions.totalPhotos
FROM Activities
JOIN (SELECT Actions.activity_id, 
             SUM(STRFTIME("%s", COALESCE(Actions.date_stopped, 'now')) 
                            - STRFTIME("%s", Actions.date_started)) AS totalSeconds, 
             SUM(COALESCE(Photos.photoCount, 0)) as totalPhotos,
             MAX(COALESCE(Actions.date_stopped, DATETIME('now'))) as mostRecent
      FROM Actions
      LEFT JOIN (SELECT action_id, COUNT(*) as photoCount
                 FROM Photos
                 GROUP BY action_id) Photos
             ON Photos.action_id = Actions._id
      GROUP BY Actions.activity_id) Actions
   ON Actions.activity_id = Activities._id
ORDER BY Actions.mostRecent DESC

(和 working result fiddle )

具体来说:

您按所有列分组(在内部查询中)。在这种情况下，您要么想要 DISTINCT (概念上/逻辑上)，或者最好将查询更改为更小。请注意，通过像我在这里的表格进行聚合，更有可能使用索引。

您按编号的列分组:始终拼出您想要的列。在极端情况下，如果有人更改 SELECT 中列的顺序列出但不列出 GROUP BY ，您的结果可能会以您意想不到的方式发生变化...不是 接收错误。

您的内部查询有 ORDER BY .这是非常不必要的，并且正在迫使引擎做额外的工作。

您的外GROUP BY只引用了一列，但有一列未聚合/分组。在这种情况下，它给出了正确的结果，但这是一个危险的特性；如果可能有多个值，则无法确定选择哪一个。默认情况下避免这种情况。

首选可用的 SQL 标准函数(除非出于特定性能原因)- IFNULL()并非在所有平台上，但 COALESCE是。除非日期/时间数学(这通常取决于 RDBMS)，否则此查询将适用于所有平台。

(顺便说一句，我对 SQLite 缺少日期/时间/时间戳类型感到恼火，但这几乎不是你的错......)