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;
(group_id, ts_week_start)
group, is it? But what's criteria to select one value from values list for a group?