MySQL連表查詢分組去重的實現示例

Posted on by

業務邏輯

通過多種渠道將小程序的活動頁鏈接發佈出去，比如通過多多種短信附帶鏈接( channel 就記為 sms1，sms2，sms3 )，或者海報上面貼微信小程序的二維碼( channel 記為 qrcode1，qrcode2，qrcode3 )，線下會員通過掃描二維碼也能進入小程序指定的活動頁，亦或者是通過其他會員分享的小程序鏈接也可以進入小程序( channel 記為 share)。這些不同的進入方式在我這篇文章統稱為不同的渠道，也就是提到的 channel 字段。從不同的渠道進入活動頁就會產生一條頁面訪問記錄。會被計入 page_view 這張表裡。

會員進入小程序的指定活動頁後，在頁面上面觸發一系列操作後，會得到相應的反饋，比如獲得積分，或者獲得優惠券等等。這步操作稱為參與活動。這條數據會被記入 activity_record 這張表裡。

現在呢，運營小姐姐要求得到一份數據報表。每位參與活動的會員是從什麼時間,哪個渠道裡面進活動的？

數據表結構

表名 member_id participate_time
activity_record 會員號 活動參與時間
表名 member_id channel view_time
page_view 會員號 渠道 頁面訪問時間

查詢邏輯

因為每位會員隻能參加一次活動，也就是活動期間隻能獲得過一次積分，或者領取過一次優惠券等等這種意思，也就是每位會員最多隻會產生一條 activity_record 記錄。

可是 page_view 這張表的記錄方式就不一樣瞭。會員可能既收到過短信鏈接，又掃描過活動二維碼，又被好友分享過活動鏈接，這下，對於這位會員來說，就會產生多條頁面訪問記錄，即在 page_view 裡產生多條數據。

你想想，會員肯定是先通過某一個渠道進入到活動頁面，才能去參加活動。也就是有多條 page_view 的數據，按照 view_time 倒序排列，總有一條的 view_time 是小於且最接近於 activity_record 的 participate_time，下一條 page_view 的 view_time 就會大於 activity_record 的 participate_time。

SQL腳本

select c.member_id,c.view_time,.channel from (
SELECT
 member_id,
 SUBSTRING_INDEX( GROUP_CONCAT( view_time ORDER BY view_time DESC ), ',', 1 ) AS view_time,
 SUBSTRING_INDEX( GROUP_CONCAT( channel ORDER BY channel DESC ), ',', 1 ) AS channel
FROM
 page_view a LEFT JOIN activity_record b
        on a.member_id = b.member_id
        where a.view_time < b.participate_time
GROUP BY
 member_id) c;

腳本說明

  • GROUP_CONCAT：通過使用distinct可以排除重復值； group_concat( [distinct] 要連接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
  • SUBSTRING_INDEX：字符串截取函數。substring_index(str,delim,count)。str:要處理的字符串；delim:分隔符；count:計數

到此這篇關於MySQL連表查詢分組去重的實現示例的文章就介紹到這瞭,更多相關MySQL連表查詢分組去重內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：