Select * from (select *, count (user_id) as ct, from_unixtime (last_update, '% % Y - m - H: % d % % I: % S') from say_bigdata where user_id!=14807 and user_id!=0 and
DATE_SUB (CURDATE (), the INTERVAL 3 MONTH) & lt;=from_unixtime (last_update, 'Y - m - % d % %) and
CONCAT (type, CONCAT (' _ ', target_id)) in (select CONCAT (type, CONCAT (' _ ', target_id)) from say_bigdata where user_id=14807 and
DATE_SUB (CURDATE (), the INTERVAL 3 MONTH) & lt;=from_unixtime (last_update, 'Y - m - % d % %)) GROUP BY user_id limit 5) s ORDER BY ct desc;
I really have no experience, advanced SQL statements for great god detailed parsing this statement is what meaning, if you can, I want to have a user statistics here again within three months of 100 data should be how to change,
Is living, the logic is as follows:
Acquire the single days or 180 days inside the browsing history of users (user_id)
And then used to find the user's browsing history, take a month before (for browsing history list)
And then analysis the browsing history of browsing user for user_id (again)
Finally the superposition,,,
Such as user_id visited page 1, 2, 3, 4, 5, 6, 7
Such as user_id 2 to access the page 1, 2, 3, 4, 5
For example user_id 3 visited page 1, 2, 3
Then overlay
1 to 2 matches the
5 times1 to 3 match 3 times
2 to 3 match 3 times
Finally I take the data of 3 months before the 100
Hope god can give directions!!!!!!
Thank humbly!!!!!!!!!!
CodePudding user response:
Online and so on, is very urgent ~ ~ ~ ~CodePudding user response:
Don't know to do ~ ~ ~CodePudding user response:
SELECT *The FROM (
# query access and user_id=14807 of 14807 users of the same target_id, queries only out of the top five
SELECT
*,
Count (user_id) AS ct,
From_unixtime (last_update, '% % Y - m - H: % d % % I: % S')
The FROM say_bigdata
WHERE user_id!=14807 AND user_id!=0 AND
DATE_SUB (CURDATE (), the INTERVAL 3 MONTH) & lt;=from_unixtime (last_update, 'Y - m - % d % %)
AND CONCAT (type, CONCAT (' _ ', target_id)) IN
(
This refers to detect user_id for 14807 # this visit for three months the target_id
SELECT CONCAT (type, CONCAT (' _ ', target_id))
The FROM say_bigdata
WHERE user_id=14807 AND
DATE_SUB (CURDATE (), the INTERVAL 3 MONTH) & lt;=
From_unixtime (last_update, 'Y - m - % d % %)
)
GROUP BY user_id
LIMIT of 5
) s
The ORDER BY ct DESC; # according to the superposition of access number of reverse order sorting,
Finally I take the data of three months before the 100 data, change the 5 to 100, (because no data, cannot guarantee absolutely correct, and I feel also have a problem with mysql write),