1

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;
9
  • You want to obtain one value for each (group_id, ts_week_start) group, is it? But what's criteria to select one value from values list for a group?
    – Akina
    Aug 14, 2018 at 8:09
  • @Akina, I want to get two values for every (group_id, ts_week_start), latest one and previous to the latest Aug 14, 2018 at 8:10
  • 1
    Specify MySQL version. 8+ or 5.x?
    – Akina
    Aug 14, 2018 at 8:12
  • @Akina, It's 5.x Aug 14, 2018 at 8:14
  • 1
    Can you pu sample data and expected result? Aug 14, 2018 at 8:40

2 Answers 2

1
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

4
  • this will only give me the most recent pairs for date '2018-01-03', right? But what I want is getting most recent pairs for every date in (2018-01-05, 2018-01-03, 2018-01-01), i.e. same query that you gave, but working with several dates. Aug 14, 2018 at 9:29
  • Alter @date variable. Or insert its value into query text as a parameter or a literal instead of using variable.
    – Akina
    Aug 14, 2018 at 9:32
  • The question is about getting pairs for different dates in a single query. Aug 14, 2018 at 9:34
  • 1
    UNION may help.
    – Akina
    Aug 14, 2018 at 9:58
1
create temporary table dates (
    d date
);
insert into dates VALUES('2018-01-02'),('2018-01-05');
SELECT
s1.group_id, s1.value, s1.ts_week_start
FROM stats_kpi AS s1,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 <= timestamp(d)
) <= 2
AND s1.group_id IN (1,2)
AND s1.ts_week_start <= timestamp(d)
ORDER BY group_id,value,s1.ts_week_start DESC;
2
  • Looks good, since it doesn't need non-standard SQL. Can we get rid of the temp table. I have one version in mind, which I'll add to the post. Aug 14, 2018 at 11:37
  • Glad to see it helped you. I am looking forward to see if anything better can be done. Aug 14, 2018 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.