How to select the highest value for a given month?

Question

+-------------------------------------------------+-----------------+---------------------+
| landing_page                                    | all_impressions | dates               |
+-------------------------------------------------+-----------------+---------------------+
| https://www.example.co.uk/url-1                 |           53977 | 2018-08-19 13:59:40 |
| https://www.example.co.uk/url-1                 |             610 | 2018-09-19 13:59:40 |
| https://www.example.co.uk/url-1                 |            555  | 2018-10-19 13:59:40 |
| https://www.example.co.uk/url-1                 |            23   | 2018-11-19 13:59:40 |
| https://www.example.co.uk/                      |            1000 | 2018-06-19 13:59:40 |
| https://www.example.co.uk/                      |               2 | 2018-07-19 13:59:40 |
| https://www.example.co.uk/                      |               4 | 2018-08-19 13:59:40 |
| https://www.example.co.uk/                      |            1563 | 2018-09-19 13:59:40 |
| https://www.example.co.uk/                      |               1 | 2018-10-19 13:59:40 |
| https://www.example.co.uk/                      |            9812 | 2018-11-19 13:59:40 |
+-------------------------------------------------+-----------------+---------------------+

With the above database table, I only want to select the landing_page if the impression count is the max for the current date - For example, from this, the select would return https://www.example.co.uk/ only as the current month it's all_impressions value is it's highest for November (https://www.example.co.uk/url-1 would not be selected as it's highest value was in August)

How might I do this with SQL?

index info:

mysql> show indexes from landing_pages_client_v3;
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| landing_pages_client_v3 |          0 | PRIMARY      |            1 | id          | A         |    24279939 |     NULL | NULL   |      | BTREE      |         |               |
| landing_pages_client_v3 |          1 | profile_id   |            1 | profile_id  | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| landing_pages_client_v3 |          1 | profile_id   |            2 | dates       | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| landing_pages_client_v3 |          1 | profile_id_2 |            1 | profile_id  | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| landing_pages_client_v3 |          1 | profile_id_2 |            2 | lp_id       | A         |     6069984 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Madhur Bhaiya · Accepted Answer · 2018-11-21T10:24:12.347

3

In a Derived Table, get the maximum value of all_impressions for every landing_page. Join back to the main table to get the row corresponding to maximum all_impressions value.

We will eventually consider that row only if it belongs to Current Month. For sargability, we will not use functions on the dates column. Instead, we will determine the first day of the current month and next month. We will consider those dates which fall within this range. You can check details of the datetime functions here: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

For performance, you may also need to add the following composite index: (landing_page, all_impressions, dates). (I am not sure about which order these columns should be in. Maybe some benchmarking/trial is needed.

SELECT
  t.* 
FROM 
  your_table AS t 
JOIN 
(
  SELECT
    landing_page, 
    MAX(all_impressions) AS max_all_impressions
  FROM your_table 
  GROUP BY landing_page
) AS dt 
  ON dt.landing_page = t.landing_page AND 
     dt.max_all_impressions = t.all_impressions 
WHERE 
 t.dates >= ((LAST_DAY(CURDATE()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH) AND
 t.dates < (LAST_DAY(CURDATE()) + INTERVAL 1 DAY)

edited Nov 21 '18 at 10:24

answered Nov 20 '18 at 16:46

Madhur Bhaiya

28,155
10
49
57

thanks for this - seems to work just very slow. any way to speed it up? – Adders Nov 20 '18 at 21:41
Looks very efficient to me. If you have not created an index on your table's 'dates' column then you are doing a full table scan. – Timothy Jannace Nov 20 '18 at 23:51
@Adders please inform what all indexing is done on your columns ? Some optimization is possible based on indexing done. – Madhur Bhaiya Nov 21 '18 at 03:56
Across 7445480 rows it took 6 hours. On smaller data sets it's better. Updated to include index info. – Adders Nov 21 '18 at 08:31
@Adders please check the updated answer. You will also need to add a composite index (landing_page, all_impressions, dates). Not sure about the order of columns in it; some trial with different order maybe required. – Madhur Bhaiya Nov 21 '18 at 09:28
`index (landing_page, all_impressions desc, dates)` still takes hours to run on bigger datasets – Adders Nov 22 '18 at 08:03
@Adders what is server configuration, like RAM, processor speed etc – Madhur Bhaiya Nov 22 '18 at 08:35
3GB of RAM - not sure on processor speed – Adders Nov 22 '18 at 09:54
@Adders this is really low config. – Madhur Bhaiya Nov 22 '18 at 09:56
you think that's the issue? – Adders Nov 22 '18 at 09:58
@Adders yes most likely. If you can access a 8 GB / 16 GB machine, some benchmarking may help. Secondly I dont like searching on string columns in MySQL. I have generally found the performance to be extremely slow. – Madhur Bhaiya Nov 22 '18 at 10:02

A l w a y s S u n n y · Answer 2 · 2018-11-20T16:40:11.057

You can try like this way to select the landing_page url and maximum value of the all_impressions column. To do that you've to use WHERE clause to check that your dates column value is the same month and year as the CURRENT_DATE number. SEE Date and Time Functions

SELECT landing_page,MAX(all_impressions) 
 FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(CURRENT_DATE())
 AND YEAR(dates) = YEAR(CURRENT_DATE())

OR

SELECT landing_page
  FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(CURRENT_DATE())
  AND YEAR(dates) = YEAR(CURRENT_DATE())
ORDER BY all_impressions DESC LIMIT 1

score 0 · Answer 3 · answered Nov 20 '18 at 16:49

0

In mysql. you can do like this.

SELECT landing_page,MAX(all_impressions) AS max_count
  FROM your_table_name_goes_here
    WHERE MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())
     GROUP BY landing_page ORDER BY max_count DESC LIMIT 1

answered Nov 20 '18 at 16:49

Syed Ausaf Hussain

133
11

thanks - this doesn't work as it returns the current month even if it's not the highest – Adders Nov 20 '18 at 21:46
set your required time period in where clause "MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())". here I use NOW() which set current Month. In this query first we get all the records of given time period, then group by landing_page on MAX aggregate AS max_count. then order by descending on max_count. then apply limit 1 which give only first record which is max – Syed Ausaf Hussain Nov 21 '18 at 11:03

How to select the highest value for a given month?

3 Answers3