MySQL 5.6.27: How to get latest pairs for different points in time in a single query

greatest-n-per-groupMySQL

I use the following query to get pairs of latest s1.value for given group ids and some fixed date. Now if I want to query pairs for different dates I have to run this query several times with different dates. My question is can I rework this query so I could query value pairs for several dates at once?

SELECT
    s1.group_id, s1.value, s1.ts_week_start
  FROM stats_kpi AS s1
  WHERE
    (SELECT COUNT(*) FROM stats_kpi as s2
       WHERE s2.group_id = s1.group_id
         AND s2.ts_week_start >= s1.ts_week_start
         AND s2.ts_week_start <= timestamp('2018-10-01')
    ) <= 2
    AND s1.group_id IN (3939)
    AND s1.ts_week_start <= timestamp('2018-10-01')
  ORDER BY s1.ts_week_start DESC;

UPD #1

Sample input:

group_id | value | ts_week_start
       1 |     1 | 2018-01-01 00:00:00
       1 |     2 | 2018-01-02 00:00:00
       1 |     3 | 2018-01-03 00:00:00
       1 |     4 | 2018-01-04 00:00:00
       1 |     5 | 2018-01-05 00:00:00
       2 |     1 | 2018-01-01 00:00:00
       2 |     2 | 2018-01-02 00:00:00
       2 |     3 | 2018-01-03 00:00:00
       2 |     4 | 2018-01-04 00:00:00
       2 |     5 | 2018-01-05 00:00:00

I want to get the following, for two groups(1, 2) and three dates (2018-01-05, 2018-01-02, 2018-01-01)
get pairs for latest and previous to latest values to the date, i.e. the desired result would be like:

group_id | value | ts_week_start
-- for group 1 and dates 2018-01-02/2018-01-01
       1 |     1 | 2018-01-01 00:00:00
       1 |     2 | 2018-01-02 00:00:00

-- for group 1 and date 2018-01-05
       1 |     4 | 2018-01-04 00:00:00
       1 |     5 | 2018-01-05 00:00:00

-- for group 2 and dates 2018-01-02/2018-01-01
       2 |     1 | 2018-01-01 00:00:00
       2 |     2 | 2018-01-02 00:00:00

-- for group 2 and date 2018-01-05
       2 |     4 | 2018-01-04 00:00:00
       2 |     5 | 2018-01-05 00:00:00

For 2018-01-01 there's only one record in the result – latest one and no previous.

Schema and data:

CREATE TABLE `stats_kpi` (
  `group_id` bigint(20) NOT NULL,
  `value` double unsigned DEFAULT NULL,
  `ts_week_start` timestamp NULL DEFAULT CURRENT_TIMESTAMP
  )

INSERT INTO `stats_kpi` VALUES (1, 1, timestamp('2018-01-01'));
INSERT INTO `stats_kpi` VALUES (1, 2, timestamp('2018-01-02'));
INSERT INTO `stats_kpi` VALUES (1, 3, timestamp('2018-01-03'));
INSERT INTO `stats_kpi` VALUES (1, 4, timestamp('2018-01-04'));
INSERT INTO `stats_kpi` VALUES (1, 5, timestamp('2018-01-05'));
INSERT INTO `stats_kpi` VALUES (2, 1, timestamp('2018-01-01'));
INSERT INTO `stats_kpi` VALUES (2, 2, timestamp('2018-01-02'));
INSERT INTO `stats_kpi` VALUES (2, 3, timestamp('2018-01-03'));
INSERT INTO `stats_kpi` VALUES (2, 4, timestamp('2018-01-04'));
INSERT INTO `stats_kpi` VALUES (2, 5, timestamp('2018-01-05'));

UPD #2

I modified proposal of Kapil Bhagchandani so it's a single query now. What I don't like is the querying dates from the stats_kpi table while I know them upfront:

SELECT
DISTINCT s1.group_id, s1.value, s1.ts_week_start
FROM stats_kpi AS s1, 
  (SELECT DISTINCT(s3.ts_week_start) AS ts FROM stats_kpi AS s3
     WHERE s3.ts_week_start IN (TIMESTAMP('2018-01-05'), TIMESTAMP('2018-01-02'), TIMESTAMP('2018-01-01'))) AS dates
WHERE(
    SELECT COUNT(*) FROM stats_kpi as s2
    WHERE s2.group_id = s1.group_id
    AND s2.ts_week_start >= s1.ts_week_start
    AND s2.ts_week_start <= dates.ts
) <= 2
AND s1.group_id IN (1,2)
AND s1.ts_week_start <= dates.ts
ORDER BY group_id,value,s1.ts_week_start DESC;

Best Answer

SET @date:='2018-01-03';

select * from ( select ta.*,
                       if(@typex=ta.group_id, 
                          @rownum:=@rownum+1, 
                          @rownum:=1+least(0,@typex:=ta.group_id)) rown 
                from stats_kpi ta, (select @rownum:=1, @typex:='_') zz
                where ts_week_start <= timestamp(@date)
                order by group_id, ts_week_start DESC
              ) yy
where rown < 3

fiddle

SUGGESTION #1

You should store the timestamp of the minute and index it

ALTER TABLE mytable ADD COLUMN MinuteTimeStamp AFTER TimeStamp;
UPDATE mytable SET MinuteTimeStamp = TimeStamp - MOD(TimeStamp,60);
ALTER TABLE mytable ADD INDEX MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey);

Then, you can do MIN aggregation on MinuteTimeStamp.

SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp;

and use it get those records

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

It was tactfully pointed out that triggers would degrade performance

Perhaps doing INSERTs like this may help

INSERT INTO mytable (UniqueKey,TimeStamp,MinuteTimeStamp) VALUES
(
    uniquevalue,
    UNIX_TIMESTAMP(NOW()),
    UNIX_TIMESTAMP(NOW() - INTERVAL SECOND(NOW()) SECOND)
);

SUGGESTION #2

Since you have over 1000 columns (Ugh), perhaps a table of those minute timestamps would be better.

CREATE TABLE MinuteKeys
(
    MinuteTimeStamp INT UNSIGNED NOT NULL,
    UniqueKey INT UNSIGNED NOT NULL,
    PRIMARY KEY (UniqueKey)
    KEY MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey)
) ENGINE=MyISAM;
ALTER TABLE MinuteKeys DISABLE KEYS;
INSERT INTO MinuteKeys SELECT TimeStamp - MOD(TimeStamp,60),UniqueKey FROM mytable;
ALTER TABLE MinuteKeys ENABLE KEYS;

Then, you could use that table for the aggregation

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM MinuteKeys GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

EPILOGUE

Other suggestions are possible but you should really consider normalization of the table

See my post Too many columns in MySQL as to why

Mysql – Unable to get the latest value added to Mysql table based on posted time

Your first HUGE mistake (as @ypercube pointed out) is not using a DATETIME type for a date-time type variable. TIMEs are not VARCHARs and doing this will mess up your queries, make your app non-portable and will confuse the optimiser.

The other HUGE problem is MySQL. It doesn't perform this sort of query properly. Your query is ambiguous - PostgreSQL for example will throw an error if you try and run such a query.

For example

mysql> SELECT id, MAX(posted_date) FROM example GROUP BY id;
+------+------------------+
| id   | MAX(posted_date) |
+------+------------------+
|    1 | 2015-03-27       |
|    2 | 2015-03-15       |
+------+------------------+
2 rows in set (0.00 sec)

mysql>

is unambiguous and gives the correct answer.But, because you have tried to use a SELECT * FROM example, the MySQL engine doesn't know which values to return and (stupidly enough) appears (at least in the several examples of this problem that I have seen) to return the correct unambiguous values, but the other are random - so you could get id = 1 (correct) with cname = 'ghi' (incorrect).

Check out MySQL's SQL modes - there's one (ONLY_FULL_GROUP_BY) that disallows this behaviour. It should be switched on at ALL times.

To get your query to work properly, for starters, use a DATETIME type (makes the subquery below easier) and then do a subquery for cname where your DATETIME value matches that for the correct cname. HTH,

[EDIT]

Using this schema and data, your query becomes relatively easy.

CREATE TABLE example
(
id int(11),
cname varchar(10),
posted_datetime datetime
); 


INSERT INTO example
VALUES (1, 'abc', '2015-03-26 04:25');

INSERT INTO example
VALUES (1, 'def', '2015-03-27 16:30');

INSERT INTO example
VALUES (2, 'ghi', '2015-03-11 02:25');

INSERT INTO example
VALUES (2, 'jkl', '2015-03-15 12:25');

And your working query is:

SELECT e.id, e.cname, e.posted_datetime 
FROM example e
  JOIN
  (
    SELECT id, MAX(posted_datetime) AS posted_datetime
    FROM example 
    GROUP BY id
  ) m
  ON  m.id = e.id 
  AND m.posted_datetime = e.posted_datetime ;

or check it out here: SQLfiddle

Best Answer

Related Solutions

Mysql – query records to fetch by time stamp

SUGGESTION #1

SUGGESTION #2

EPILOGUE

Mysql – Unable to get the latest value added to Mysql table based on posted time

Related Question