Mysql
可以將此查詢重寫為聯接嗎?
我相當確定可以將其重寫為連接查詢,但我只是不確定如何處理它:
SELECT events.*, (SELECT MAX(updated_at) FROM events e WHERE e.user_id = events.user_id AND type = 'follow' AND e.updated_at < events.updated_at AND events.type = 'unfollow' ) AS last_follow_date FROM events WHERE user_id = 1;
數據庫結構:
CREATE TABLE `events` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `user_id` int(10) unsigned NOT NULL, `type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `follower` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `follower_pk` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `events_user_id_foreign` (`user_id`), CONSTRAINT `events_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `users_email_unique` (`email`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
一些數據:
INSERT INTO `users` (`id`, `name`, `email`) VALUES (1, 'Aydin Hassan', 'email@website.com'); INSERT INTO `events` (`id`, `created_at`, `updated_at`, `user_id`, `type`, `follower`, `follower_pk`) VALUES (1, '2015-01-01 00:00:00', '2015-01-01 00:00:00', 1, 'follow', 'annie.leibovitz', '10'), (2, '2015-04-04 00:00:00', '2015-04-04 00:00:00', 1, 'unfollow', 'annie.leibovitz', '10'), (3, '2015-04-04 00:00:00', '2015-04-04 00:00:00', 1, 'follow', 'edward.weston', '11'), (5, '2015-10-10 00:00:00', '2015-10-10 00:00:00', 1, 'follow', 'annie.leibovitz', '10'), (4, '2017-01-01 10:10:00', '2017-01-01 10:10:00', 1, 'unfollow', 'annie.leibovitz', '10');
該表儲存有關臨時社交平台上使用者的關注者的事件。查詢正在添加(如果事件是取消關注類型)關注者開始關注使用者的最後日期。
正確的結果應該是:
+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+ | id | created_at | updated_at | user_id | type | follower | follower_pk | last_follow_date | +----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+ | 1 | 2015-01-01 00:00:00 | 2015-01-01 00:00:00 | 1 | follow | annie.leibovitz | 10 | NULL | | 2 | 2015-04-04 00:00:00 | 2015-04-04 00:00:00 | 1 | unfollow | annie.leibovitz | 10 | 2015-01-01 00:00:00 | | 3 | 2015-04-04 00:00:00 | 2015-04-04 00:00:00 | 1 | follow | edward.weston | 11 | NULL | | 4 | 2017-01-01 10:10:00 | 2017-01-01 10:10:00 | 1 | unfollow | annie.leibovitz | 10 | 2015-10-10 00:00:00 | | 5 | 2015-10-10 00:00:00 | 2015-10-10 00:00:00 | 1 | follow | annie.leibovitz | 10 | NULL | +----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+
作為一個額外的問題:是否有更好的方法來儲存和查詢這些數據?
您可以使用以下
JOIN
ed 查詢獲得所需的內容,該查詢與您的原始查詢非常相似:SELECT e.id, e.created_at, e.updated_at, e.user_id, e.type, e.follower, e.follower_pk, max(e2.updated_at) AS last_follow_date FROM events e LEFT JOIN events e2 ON e2.user_id = e.user_id AND e2.type = 'follow' AND e2.updated_at < e.updated_at AND e.type = 'unfollow' WHERE e.user_id = 1 GROUP BY e.id ORDER BY e.id, e.updated_at ;
關於性能,您的查詢將受益於索引,例如:
-- Index to improve performance CREATE INDEX idx_events_user_id_type_updated_at ON events (user_id, type, updated_at) ;
三列
user_id
,type
並updated_at
出現在JOIN
條件中。您想要它們的順序是user_id
(您進行 = 比較,並且應該具有高基數 - 即:非常多的值 -),type
(您進行 = 比較,基數 = 2),updated_at
檢查不等式。 .. MySQL 不足以將其用作索引查找的一部分,但會將索引設為covering
一個,這樣可以避免檢查原始表。替代方案:對原始查詢稍作改動,可能會節省一些時間:
SELECT e1.*, case when type = 'unfollow' then (SELECT MAX(e2.updated_at) FROM events e2 WHERE e2.user_id = e1.user_id AND e2.type = 'follow' AND e2.updated_at < e1.updated_at ) else NULL end AS last_follow_date FROM events e1 WHERE e1.user_id = 1;
你可以在*這裡*查看dbfiddle的所有內容