Geek asks and answers

A query to find the 16 most secure posts in the last 30 days without duplicate categories

I have a seemingly simple task, but I cannot find an elegant solution using 1 query ...

Problem: I have a table of recorded “clicks” on “posts”, where each post is part of a “category”. I want to find 16 items with the highest click in the last 30 days, but I want to avoid duplication of categories.

It seems very simple actually, but I seem to be stuck.

I know how to get the most clicks in the last 30, but I cannot figure out how to avoid duplicating cats.

SELECT cat_id,
       post_id,
       COUNT(post_id) AS click_counter
FROM   cs_coupon_clicks
WHERE  time_of_click > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP  BY post_id
ORDER  BY click_counter DESC

I tried to get creative / hack it ... this is close but not correct:

SELECT cat_id,
       Max(sort) AS sortid
FROM   (SELECT cat_id,
               post_id,
               COUNT(post_id)                       AS click_counter,
               CONCAT(COUNT(post_id), '-', post_id) AS sort
               FROM   cs_coupon_clicks
               WHERE  time_of_click > DATE_SUB(NOW(), INTERVAL 30 DAY)
               GROUP  BY cat_id, post_id) t1
GROUP  BY cat_id
ORDER  BY cat_id ASC

, MySQL. , PHP, , .

, .

EDIT ():

CREATE TABLE `cs_coupon_clicks` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`src` varchar(255) NOT NULL DEFAULT '',
`cat_id` int(20) NOT NULL,
`post_id` int(20) NOT NULL,
`tag_id` int(20) NOT NULL,
`user_id` int(20) DEFAULT NULL,
`ip_address` char(30) DEFAULT NULL,
`referer` varchar(255) NOT NULL,
`browser` varchar(10) DEFAULT NULL,
`server_var` text NOT NULL,
`time_of_click` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `cat_id` (`cat_id`),
KEY `post_id` (`post_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

(HACKY):

SELECT
  cat_id,
  MAX(sort) AS sortid
FROM (
  SELECT
    cat_id,
    post_id,
    COUNT(post_id) AS click_counter,
    RIGHT(Concat('00000000', COUNT(post_id), '-', post_id), 16) AS SORT
  FROM   cs_coupon_clicks
  WHERE  time_of_click > DATE_SUB(NOW(), INTERVAL 30 DAY)
  GROUP  BY cat_id, post_id
) AS t1
GROUP  BY cat_id
ORDER  BY sortid DESC
+3
2

, , ( ), . , ( php-), , , :

CREATE TABLE `click_cnts` (
 `cat_id` int(20) NOT NULL,
 `post_id` int(20) NOT NULL,
 `clicks` int(20) NOT NULL,
 PRIMARY KEY (`cat_id`,`post_id`),
 KEY `cat_id` (`cat_id`,`clicks`)
)

, , :

INSERT INTO click_cnts(cat_id, post_id, clicks)
SELECT cat_id, post_id, COUNT(post_id) AS click_counter
      FROM   cs_coupon_clicks
      WHERE  time_of_click > NOW() - INTERVAL 30 DAY
      GROUP  BY cat_id,post_id

( ? , ...) , , :

SELECT cg.cat_id, cu.post_id, cg.most_clicks 
FROM
( SELECT cat_id, max(clicks) as most_clicks FROM click_cnts
  GROUP BY cat_id ) cg
JOIN click_cnts cu 
ON cg.cat_id = cu.cat_id
AND cu.post_id = ( SELECT cc.post_id FROM click_cnts cc
                   WHERE cc.cat_id = cg.cat_id
                   AND cc.clicks = cg.most_clicks
                   LIMIT 1 )
ORDER BY cg.most_clicks DESC
LIMIT 16
+2

. Select DISTINCT cat_id

0

More articles:


All Articles