iT邦幫忙

0

MYSQL 代替lead()的寫法?

各位好,目前有個問題是希望查詢一分鐘有3次以上的交易,
每筆交易為基準後兩次交易時間要在1分內,
以範例來說 希望可以查出 001,005,007這三張單
雖然007往下兩張的交易時間是00:02:10超過1分鐘
但是在001那次查詢的時候就已經被劃分為需要的資料

原本的資料
+-----------+---------------------+
|   交易單號  |      datetime       |
+-----------+---------------------+
|    001    | 2019-01-01 00:00:00 |
|    005    | 2019-01-01 00:00:30 |
|    007    | 2019-01-01 00:00:59 |
|    008    | 2019-01-01 00:01:42 |
|    050    | 2019-01-01 00:02:10 |
+-----------+---------------------+

接下來我自己的預想是先將下兩筆的時間查詢出來再做timediff 如下表
雖然有邦友告訴我有lead()這個函式,不過很可惜sever版本才5.5.6.... lead()要到8才支援ORZ...

想要得到的資料
+----+----------+--------------------------------+---------------------+
| id |      datetime       |    下一筆datetime    |   下兩筆datetime     |
+----+---------------------+---------------------+---------------------+
|  1 | 2019-01-01 00:00:00 | 2019-01-01 00:00:30 | 2019-01-01 00:00:59 |
|  2 | 2019-01-01 00:00:30 | 2019-01-01 00:00:59 | 2019-01-01 00:01:42 |
|  3 | 2019-01-01 00:00:59 | 2019-01-01 00:01:42 | 2019-01-01 00:02:10 |
|  4 | 2019-01-01 00:01:42 | 2019-01-01 00:02:10 |                NULL |
|  5 | 2019-01-01 00:02:10 |                NULL |                NULL |
+----+---------------------+---------------------+---------------------+

然後我依照這個思路想出了如下的查詢,但是發現子查詢出來的表a沒辦法拿給其他子查詢使用....

SET @row = 0;
SET @row1 = 1;
SELECT *
,(SELECT datetime FROM a WHERE novalue = @row1 ORDER BY novalue LIMIT 0,1) as next_time1
,(SELECT datetime FROM a WHERE novalue = @row1+1 ORDER BY novalue LIMIT 0,1) as next_time2
FROM
(
SELECT @row:=@row+1 AS novalue, trxn.*
FROM trxn
) AS a

感謝各位看了這麼落落長的問題描述(心路歷程?)之後,最後小弟想要請教一下
1.我這個使用變數來查詢的思路是否正確只是語法錯誤? 如果是要怎麼改寫才能夠讓他正常執行?
2.有沒有其他辦法來解這個問題?

2 個回答

1
浩瀚星空
iT邦超人 1 級 ‧ 2020-03-10 17:38:42

不是固定時間內的浮動基數資料。
這確實有點麻煩

我看你用+1記錄的方式。
其實我會用+1分的方式來取數量有無超過2筆。

目前暫時是這樣想。只是我不太明白你後面的說明。
還有要列出來的資料情況。

1
一級屠豬士
iT邦大師 1 級 ‧ 2020-03-10 17:59:52
create table it200310a (
  id int unsigned not null auto_increment primary key
, txn char(3) not null
, txdt datetime not null
);

insert into it200310a(txn, txdt) values
('001', timestamp '2019-01-01 00:00:00'),
('005', timestamp '2019-01-01 00:00:30'),
('007', timestamp '2019-01-01 00:00:59'),
('008', timestamp '2019-01-01 00:01:42'),
('050', timestamp '2019-01-01 00:02:10');
---
增加一筆資料,讓時間順序(txdt) 與 id, txn 錯位,
以確定後續語法的正確性.

insert into it200310a(txn, txdt) values
('009', timestamp '2019-01-01 00:01:30');
-----
依照 txdt 產生 流水序號

select txn
     , txdt
     , @n := @n + 1 as sn
  from it200310a
     , (select @n := 0) a
 order by txdt;

+-----+---------------------+------+
| txn | txdt                | sn   |
+-----+---------------------+------+
| 001 | 2019-01-01 00:00:00 |    1 |
| 005 | 2019-01-01 00:00:30 |    2 |
| 007 | 2019-01-01 00:00:59 |    3 |
| 009 | 2019-01-01 00:01:30 |    4 |
| 008 | 2019-01-01 00:01:42 |    5 |
| 050 | 2019-01-01 00:02:10 |    6 |
+-----+---------------------+------+
6 rows in set, 2 warnings (0.00 sec)

經觀察,是依照 txdt 由小到大產生序號.
------------
接著就可以加工了.會很囉唆又無聊的一段.

select *
  from (select txn
             , txdt
             , @an := @an + 1 as sn
          from it200310a
             , (select @an := 0) a
          order by txdt
       ) i
  left join
       (select txn
             , txdt
             , @bn := @bn + 1 as sn
          from it200310a
             , (select @bn := 0) b
          order by txdt
       ) j
    on i.sn = j.sn - 1
  left join
       (select txn
             , txdt
             , @cn := @cn + 1 as sn
          from it200310a
             , (select @cn := 0) c
          order by txdt
       ) k
      on i.sn = k.sn - 2
 ;

https://ithelp.ithome.com.tw/upload/images/20200310/20050647FL7nB06EwX.png

看更多先前的回應...收起先前的回應...
各位好,目前有個問題是希望查詢一分鐘有3次以上的交易,
每筆交易為基準後兩次交易時間要在1分內 

你這句話,不是很理解.
你把你真的想做到的,描述清楚一些,現在好像是繞彎路了.

ckp6250 iT邦研究生 4 級 ‧ 2020-03-10 21:00:19 檢舉

又受教了,我之前還不知道 lead() 這個寶物,
類似問題都用土法練鋼,反正 sql 萬能。

看來,我的惰性還不小,只問能解決問題,
沒有再去研讀新觀念。

window functions 超好用喔.只是MySQL 以前一直停在
ANSI SQL92 標準,造成蠻多人誤以為資料庫就都是那樣.

甲土豆 iT邦新手 5 級 ‧ 2020-03-11 09:29:00 檢舉

SQL 大神啊 /images/emoticon/emoticon32.gif

jasonb122 iT邦新手 5 級 ‧ 2020-03-11 10:59:30 檢舉

抱歉回慢了,我昨天後來也是採用類似的解法,讓ID錯位再JOIN產生後面交易的時間,再做處理。
大概長這樣

SET @row = 0;
SET @row1 = -1;
SET @row2 = -2;
SELECT *
FROM
(
	SELECT @row:=@row+1 AS novalue, `trxn_id`, `trxn_date` 
	FROM `trxn` 
) AS a
INNER JOIN
(
	SELECT @row1:=@row1+1 AS novalue, `trxn_id`, `trxn_date` 
	FROM `trxn` 
) AS b
INNER JOIN
(
	SELECT @row2:=@row2+1 AS novalue, `trxn_id`, `trxn_date` 
	FROM `trxn` 
) AS c
WHERE a.novalue = b.novalue
AND a.novalue = c.novalue

至於@一級屠豬士說的描述問題,我覺得我的描述能力還有待加強,常常被這樣說/images/emoticon/emoticon02.gif

jasonb122 iT邦新手 5 級 ‧ 2020-03-11 11:02:37 檢舉

現在在測試大大的解法,目前還沒完全看懂,SQL真是博大精深/images/emoticon/emoticon17.gif

我要發表回答

立即登入回答