php - 这个 SQLite 查询有效吗？

Question

我在编写一些代码时玩得很开心访客柜台 .这是一个 PHP5/SQLite3 混合。

制作 两个数据库表 , 一个用于 访客 , 一个用于 点击 .结构和样本数据:

CREATE TABLE 'visitors' (
'id' INTEGER DEFAULT NULL PRIMARY KEY AUTOINCREMENT,
'ip' TEXT DEFAULT NULL,
'hash' TEXT DEFAULT NULL,
UNIQUE(ip)
);

INSERT INTO "visitors" ("id","ip","hash") VALUES ('1','1.2.3.4','f9702c362aa9f1b05002804e3a65280b');
INSERT INTO "visitors" ("id","ip","hash") VALUES ('2','1.2.3.5','43dc8b0a4773e45deab131957684867b');
INSERT INTO "visitors" ("id","ip","hash") VALUES ('3','1.2.3.6','9ae1c21fc74b2a3c1007edf679c3f144');

CREATE TABLE 'hits' (
'id' INTEGER DEFAULT NULL PRIMARY KEY AUTOINCREMENT,
'time' INTEGER DEFAULT NULL,
'visitor_id' INTEGER DEFAULT NULL,
'host' TEXT DEFAULT NULL,
'location' TEXT DEFAULT NULL
);

INSERT INTO "hits" ("id","time","visitor_id","host","location") VALUES ('1','1418219548','1','localhost','/some/path/example.php');
INSERT INTO "hits" ("id","time","visitor_id","host","location") VALUES ('2','1418219550','1','localhost','/some/path/example.php');
INSERT INTO "hits" ("id","time","visitor_id","host","location") VALUES ('3','1418219553','1','localhost','/some/path/example.php');
INSERT INTO "hits" ("id","time","visitor_id","host","location") VALUES ('4','1418219555','2','localhost','/some/path/example.php');
INSERT INTO "hits" ("id","time","visitor_id","host","location") VALUES ('5','1418219557','1','localhost','/some/path/example.php');
INSERT INTO "hits" ("id","time","visitor_id","host","location") VALUES ('6','1418219558','3','localhost','/some/path/example.php');

我现在想获取访问者数据，但仅限于那些在最后 30 秒内活跃的人。我需要以下数据作为输出，这里以用户 ID 1 为例:

$visitor = Array(
    [id] => 1
    [ip] => 1.2.3.4
    [hash] => f9702c362aa9f1b05002804e3a65280b
    [first_hit] => 1418219548
    [last_hit] => 1418219557
    [last_host] => localhost
    [last_location] => /some/path/example.php
    [total_hits] => 4
    [idle_since] => 11
)

我会用我当前的查询得到这个，一切都很好，但正如你所看到的，我需要很多子选择:

SELECT 
   visitors.id,
   visitors.ip,
   visitors.hash,
   (SELECT hits.time FROM hits WHERE hits.visitor_id = visitors.id ORDER BY hits.id ASC LIMIT 1) AS first_hit,
   (SELECT hits.time FROM hits WHERE hits.visitor_id = visitors.id ORDER BY hits.id DESC LIMIT 1) AS last_hit,
   (SELECT hits.host FROM hits WHERE hits.visitor_id = visitors.id ORDER BY hits.id DESC LIMIT 1) AS last_host,
   (SELECT hits.location FROM hits WHERE hits.visitor_id = visitors.id ORDER BY hits.id DESC LIMIT 1) AS last_location,
   (SELECT COUNT(hits.id) FROM hits WHERE hits.visitor_id = visitors.id) AS total_hits,
   (SELECT strftime('%s','now') - hits.time FROM hits WHERE hits.visitor_id = visitors.id ORDER BY hits.id DESC LIMIT 1) AS idle_since
FROM visitors
WHERE idle_since < 30
ORDER BY last_hit DESC

所以， 这对我的用例来说可以吗，还是您知道从这两个表中获取这些数据的更好方法？ 我已经玩过 JOINS，但无论我如何调整它，COUNT() 都会给我错误的输出，例如用户 id 1 只有一次总命中。

我可能必须重新建模数据库，如果我想正确使用 JOINS，我猜。

更新 :基于 AeroX 的答案，我已经建立了新的查询。它基本上只有一个小错误。 WHERE 子句中不能有 MAX() 。现在在 GROUPING 之后使用 HAVING。
我还用 EXPLAIN 和 EXPLAIN QUERY PLAN 测试了旧的和新的。看起来好多了。谢谢你们!

SELECT
    V.id,
    V.ip,
    V.hash,
    MIN(H.time) AS first_hit,
    MAX(H.time) AS last_hit,
    strftime('%s','now') - MAX(H.time) AS idle_since,
    COUNT(H.id) AS total_hits,
    LH.host AS last_host,
    LH.location AS last_location
FROM visitors AS V

INNER JOIN hits AS H ON (V.id = H.visitor_id)

INNER JOIN (
    SELECT visitor_id, MAX(id) AS id
    FROM hits
    GROUP BY visitor_id
) AS L ON (V.id = L.visitor_id)

INNER JOIN hits AS LH ON (L.id = LH.id)

GROUP BY V.id, V.ip, V.hash, LH.host, LH.location

HAVING idle_since < 30

ORDER BY last_hit DESC

Answer 1

您可能想要清理它，但这应该让您了解如何进行连接以及如何使用 GROUP BY语句来汇总每个访问者的命中表。这应该比使用大量子查询更有效。

我已经包含了对连接的评论，以便您可以了解我制作它们的原因。

SELECT 
   V.id,
   V.ip,
   V.hash,
  MIN(H.time) AS first_hit,
  MAX(H.time) AS last_hit,
  COUNT(H.id) AS total_hits,
  strftime('%s','now') - MAX(H.time) AS idle_since,
  LH.host AS last_host,
  LH.location AS last_location
FROM visitors AS V
-- Join hits table so we can calculate aggregates (MIN/MAX/COUNT)
INNER JOIN hits AS H ON (V.id = H.visitor_id)
-- Join a sub-query as a table which contains the most recent hit.id for each visitor.id
INNER JOIN (
  SELECT visitor_id, MAX(id) AS id
  FROM hits
  GROUP BY visitor_id
) AS L ON (V.id = L.visitor_id)
-- Use the most recent hit.id for each visitor.id to fetch that most recent row (for last_host/last_location)
INNER JOIN hits AS LH ON (L.id = LH.id)
GROUP BY V.id, V.ip, V.hash, LH.host, LH.location
HAVING idle_since < 30
ORDER BY last_hit DESC