使用MySQL5.5比較后續行之間的時間-有解無憂

這是我tusers在 MySQL 5.5.1 資料庫社區版上的表

mysql> SELECT * FROM `tusers`;
 ------------ ------------ ---------- ----- 
| tIDUSER    | tDate      | tHour    | tID |
 ------------ ------------ ---------- ----- 
| Controneri | 2022-01-06 | 07:54:42 |   1 |
| Controneri | 2022-01-06 | 07:43:38 |   2 |
| Controneri | 2022-01-06 | 07:13:09 |   3 |
| Controneri | 2022-01-06 | 06:31:52 |   4 |
| Controneri | 2022-01-06 | 06:13:12 |   5 |
 ------------ ------------ ---------- ----- 
5 rows in set (0.13 sec)

我只需要從表中選擇tusers這些行

 ------------ ------------ ---------- ----- 
| tIDUSER    | tDate      | tHour    | tID |
 ------------ ------------ ---------- ----- 
| Controneri | 2022-01-06 | 07:43:38 |   2 |
| Controneri | 2022-01-06 | 06:13:12 |   5 |
 ------------ ------------ ---------- -----

因為Controneri與前一行相比，其他行在一小時內為同一用戶重復。

每個用戶對網頁的訪問都存盤在表 tusers 上的日期和時間。但是我必須只提取第一次訪問并排除一小時內的重復訪問。在此示例中，用戶Controneri在 1 月 6 日登錄了 5 次。但是有效的訪問是06:13:12和07:43:38，因為在 06:13:12 訪問之后之前還有其他訪問07:13:12，即與小時相比，在小時結束之前06:13:12（06:31:52和07:13:09，第 4 行和第 3 行）。

我試過沒有成功。

我的表結構和Select query下面的db-fiddle.com，它提供 MySQL 5

有什么建議嗎？

-- ----------------------------
-- Table structure for tusers
-- ----------------------------
DROP TABLE IF EXISTS `tusers`;
CREATE TABLE `tusers`  (
  `tIDUSER` varchar(255) NULL DEFAULT NULL,
  `tDate` date NULL DEFAULT NULL,
  `tHour` time NULL DEFAULT NULL,
  `tID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`tID`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of tusers
-- ----------------------------
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '07:54:42', 1);
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '07:43:38', 2);
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '07:13:09', 3);
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '06:31:52', 4);
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '06:13:12', 5);

SELECT
        a.tID,
        a.tDate,
        a.tHour,
        a.tIDUSER,
        TIMEDIFF( a.tHour, b.tHour ) AS tDif 
    FROM
        `tusers` a
        JOIN `tusers` b ON 
        a.tDate = b.tDate 
        AND a.tIDUSER = b.tIDUSER 
        AND a.tID > b.tID 
    WHERE
        ( TIMEDIFF( a.tHour, b.tHour ) BETWEEN '00:00:00' AND '01:00:00' ) 
    ORDER BY
        a.tIDUSER,
        a.tDate,
        a.tHour ASC;

uj5u.com熱心網友回復：

對于 MySQL 5.5，您可以通過跟蹤用戶變數中的先前值來實作這一點 -

SELECT tIDUSER, tDate, tHour, tID
FROM (
    SELECT
        tusers.*,
        IF(@prev_date_time IS NULL OR @prev_user <> tIDUSER OR @prev_date_time   INTERVAL 1 HOUR < TIMESTAMP(tDate, tHour), @prev_date_time := TIMESTAMP(tDate, tHour), NULL) AS `show`,
        @prev_user := tIDUSER
    FROM tusers, (SELECT @prev_date_time := NULL, @prev_user := NULL) n
    ORDER BY tIDUSER ASC, tDate ASC, tHour ASC
) t
WHERE `show` IS NOT NULL
ORDER BY tIDUSER ASC, tDate ASC, tHour ASC;

這是一個db<>fiddle。感謝粘性位，因為我冒昧地從他們的db<>fiddle中“借用” 。

在MySQL的5.6手動狀態-

但是，涉及用戶變數的運算式的求值順序是未定義的。

在后來的版本中被擴展為 -

涉及用戶變數的運算式的求值順序未定義。例如，不能保證 SELECT @a, @a:=@a 1 先計算 @a 然后再執行賦值。

在MySQL的5.7手動也狀態-

也可以在 SET 以外的陳述句中為用戶變數賦值。（此功能在 MySQL 8.0 中已棄用，并可能在后續版本中洗掉。）以這種方式進行賦值時，賦值運算子必須是 := 而不是 = 因為后者在陳述句中被視為比較運算子 =放：

盡管有上述警告，但這種方法已被廣泛使用多年。你的旅費可能會改變。

我懷疑這會在較大的結果集上表現不佳，但請嘗試一下。

uj5u.com熱心網友回復：

根據評論中 OP 的要求，這里是一個使用遞回 CTE 的查詢，它將在 MySQL 8 及更高版本中可用。

WITH RECURSIVE
cte1
AS
(
SELECT tusers.tiduser,
       tusers.tdate,
       tusers.thour,
       tusers.tid,
       addtime(tusers.tdate, tusers.thour) AS sane_timestamp_representation,
       row_number() OVER (PARTITION BY tusers.tiduser
                          ORDER BY addtime(tusers.tdate, tusers.thour) ASC) AS rn
       FROM tusers
),
cte2
AS
(
SELECT cte1.tiduser,
       cte1.tdate,
       cte1.thour,
       cte1.tid,
       cte1.sane_timestamp_representation,
       0 AS n
       FROM cte1
UNION ALL
SELECT cte1.tiduser,
       cte1.tdate,
       cte1.thour,
       cte1.tid,
       cte1.sane_timestamp_representation,
       cte2.n   1 AS n
       FROM cte2
            INNER JOIN cte1
                       ON cte2.tiduser = cte1.tiduser
                          AND cte1.sane_timestamp_representation > adddate(cte2.sane_timestamp_representation, INTERVAL 1 HOUR)
),
cte3
AS
(
SELECT cte2.tiduser,
       cte2.tdate,
       cte2.thour,
       cte2.tid,
       cte2.sane_timestamp_representation,
       row_number() OVER (PARTITION BY cte2.tiduser,
                                       cte2.n
                          ORDER BY cte2.sane_timestamp_representation ASC) rn
       FROM cte2
)
SELECT cte3.tiduser,
       cte3.tdate,
       cte3.thour,
       cte3.tid
       FROM cte3
       WHERE cte3.rn = 1
       ORDER BY cte3.tiduser ASC,
                cte3.sane_timestamp_representation ASC;

db<>小提琴

1.cte1我們首先將時間戳的日期和小時部分合并（將它們保存為兩個不同的列不是最明智的想法；當必須跨越日期邊界時會變得一團糟）。我們還row_number() rn根據每個用戶按升序分配時間戳。cte1從現在開始充當我們的“基表”。

2. 現在cte2遞回發生了。作為錨，我們從cte1where獲取所有行cte1.rn = 1。這些是具有該用戶最小時間戳的用戶的記錄。我們還添加了一些數字n。對于那些初次錨行我們設定n到0。n將充當指示哪些行不能相互覆寫。帶有n xfor 的所有行x > 1不能被帶有n（每個用戶）的任何行覆寫。

在遞回步驟中，我們將cte1每個用戶過去一小時的所有記錄連接起來。由于這些不能被記錄已經在結果集（每用戶）所覆寫，它們是過去的一個小時，我們指定n 1為n他們。

3. cte3增加了一個row_number() rn由每時間戳升序排列的記錄user和n。具有rnof 的1那些不會被用戶的任何先前記錄所覆寫，因為具有相等或更大的所有其他n時間戳具有更大的時間戳，而具有較小時間戳的那些n不會按照我們構建的那樣覆寫它們n。所以我們可以從cte3哪里選擇這些記錄rn = 1，得到我們的最終結果。

不過，一個大胖警告：

中間結果集會快速增長！您可以嘗試從不cte3帶WHERE子句的情況下進行選擇并自己查看。因此，盡管這表明它可以做到在理論上，它可能不是實際的，即使是中等的資料集。所需資源可能很快超過最大值。
（而且，由于具有遞回 CTE 的 AFAIK SQL 是圖靈完備的，并且問題似乎可以很好地計算，很明顯它無論如何都可以完成。但我認為看到一個如何完成它的示例仍然很有趣。）

也許它可以被優化。我相信，關鍵是在遞回步驟中限制連接的行。我們實際上只需要加入一個小時后最舊的記錄，這將是下一個感興趣的記錄。這也將使得cte3與WHERE最終SELECT不必要的（除非投影擺脫助手列）。但是我沒有找到這樣做的方法。LIMIT以及遞回 CTE 不允許或實作視窗函式，至少在遞回步驟中是這樣。但是如果有人提出這樣的優化，我很樂意看到它！

哦，兩列中愚蠢的時間戳表示，首先需要放在一起，也會導致無法在時間戳上使用索引。所以這是另一個限制性能的因素。

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/406047.html

標籤：

上一篇：如何根據形式化規則在MySQL中存盤行的狀態？

下一篇：mySQL創建表唯一索引