Example of implementing grouping and deduplication in MySQL table join query

Business Logic

Publish the link to the mini program's activity page through various channels, such as sending links via text messages (channels are recorded as sms1, sms2, sms3), or posting the WeChat mini program's QR code on a poster (channels are recorded as qrcode1, qrcode2, qrcode3). Offline members can also enter the mini program's designated activity page by scanning the QR code, or they can enter the mini program through the mini program link shared by other members (channel is recorded as share). These different entry methods are collectively referred to as different channels in this article, which is the channel field mentioned. Entering the activity page from different channels will generate a page visit record. It will be counted in the page_view table.

After members enter the designated activity page of the mini program and trigger a series of operations on the page, they will receive corresponding feedback, such as earning points or coupons, etc. This step is called participation. This data will be recorded in the activity_record table.

Now, the operations lady asked for a data report. At what time and through which channel did each member participating in the event enter the event?

Data table structure

Query logic

Because each member can only participate in one activity, that is, they can only earn points once during the activity, or receive a coupon once, etc., which means that each member will only generate one activity_record record at most.

But the recording method of the page_view table is different. A member may have received a link via SMS, scanned an event QR code, or had the event link shared by a friend. This will generate multiple page access records for this member, i.e., multiple data in page_view.

Think about it, members must first enter the event page through a certain channel before they can participate in the event. That is, if there are multiple page_view data, arranged in reverse order by view_time, there will always be a view_time that is smaller than and closest to the participate_time of the activity_record, and the view_time of the next page_view will be larger than the participate_time of the activity_record.

SQL Scripts

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;

Script Description

• GROUP_CONCAT: By using distinct, duplicate values ​​can be excluded; group_concat( [distinct] field to be connected [order by sort field asc/desc] [separator 'separator'] )
• SUBSTRING_INDEX: string interception function. substring_index(str,delim,count). str: string to be processed; delim: separator; count: count
  

This concludes this article about the implementation example of MySQL table-joined query grouping and deduplication. For more information about MySQL table-joined query grouping and deduplication, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!