Return all group values divided by first value in group

Question

I have a query

SELECT GROUP, VALUE, UNIXTIME FROM TABLE1

that returns a table that looks like this:

GROUP    VALUE    UNIXTIME
A        866      1522540800
A        123      1525132800
A        100      1527811200
A        85       1530403200
A        77       1533081600
A        65       1535760000
B        376      1522540800
B        66       1525132800
B        45       1527811200
B        58       1530403200
B        42       1533081600
C        481      1522540800
C        68       1525132800
C        77       1527811200
C        50       1530403200
D        792      1522540800
D        126      1525132800
D        84       1527811200
E        1297     1522540800
E        203      1525132800
F        882      1522540800

How can I get a result that returns the same result, but where each row is divided by the first value in its own group.

So for example VALUE on

row1 should be 866/866 = 1
row2 should be 123/866 = 0.142
row3 = 100/866 = 0.115
Value on row7 (first row of group B) should be 376/376=1
row8 should be 66/376 = 0.176 etc

What is the definition of first value in each group ? Do you have a primary key (id) based on which first value can be defined ? — Madhur Bhaiya, Sep 18 '18 at 14:58
@MadhurBhaiya Just guessing, but I suspect it's the one with the lowest timestamp — Strawberry, Sep 18 '18 at 15:03
to add to Strawberry 's comment a UNIXTIME/DATETIME datatype column can also be used to identify a row order in MySQL @MadhurBhaiya "Do you have a primary key (id) based on which first value can be defined ?" Not only a primary key column with auto_increment options is need to do this. — Raymond Nijland, Sep 18 '18 at 15:11

Madhur Bhaiya · Answer 1 · 2018-09-18T15:24:03.660

1

Try the following (will work for all versions of MySQL, especially < 8.0):

SELECT t3.*, t3.VALUE / t2.FIRST_VALUE AS RATIO 
FROM TABLE1 AS t3 
INNER JOIN (SELECT t1.GROUP, 
                   t1.VALUE AS FIRST_VALUE 
            FROM TABLE1 AS t1 
            WHERE t1.UNIXTIME = (SELECT MIN(UNIXTIME) 
                                 FROM TABLE1 AS t4 
                                 WHERE t4.GROUP = t1.GROUP) 
            GROUP BY t1.GROUP) AS t2 ON t2.GROUP = t3.GROUP

Another possible solution utilizing GROUP_CONCAT and string operations is:

SELECT t3.*, t3.VALUE / t2.FIRST_VALUE AS RATIO 
FROM TABLE1 AS t3 
INNER JOIN (SELECT t1.GROUP, 
                   CAST(SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT t1.VALUE ORDER BY t1.UNIXTIME ASC SEPARATOR ','), ',', 1) AS UNSIGNED) AS FIRST_VALUE
            FROM TABLE1 AS t1 
            GROUP BY t1.GROUP) AS t2 ON t2.GROUP = t3.GROUP

Note: You might need to increase the group_concat_max_len if you have more values in a particular group.

edited Sep 18 '18 at 15:24

answered Sep 18 '18 at 15:12

Madhur Bhaiya

28,155
10
49
57

1

i see you using `GROUP_CONCAT` "hack" "alot" you are aware off the default GROUP_CONCAT length of 1024 right? – Raymond Nijland Sep 18 '18 at 15:13
@RaymondNijland yes I am aware. For practical purposes, it works though. Anyways `set group_concat_max_len` can be used – Madhur Bhaiya Sep 18 '18 at 15:14
1

"Anyways set group_concat_max_len can be used " i know... You should say it in your posts the topicstarters are most likely not aware off this GROUP_CONCAT limit causing bad results on real tabledata sizes. – Raymond Nijland Sep 18 '18 at 15:15
@RaymondNijland I have added one more possible way without using Group_concat :) – Madhur Bhaiya Sep 18 '18 at 15:24
Thanks for the help. Both solutions work well so I marked the other one as it was first answer, seems most fair .. – digestivee Sep 18 '18 at 15:47
@digestivee it's ok. No worries ! But actually my answer was first, if you look at timestamps in detail :) doesnt matter though. Happy to help :) – Madhur Bhaiya Sep 18 '18 at 16:29

score 1 · Accepted Answer · answered Sep 18 '18 at 15:20

Consider the following...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(group_id INT NOT NULL
,unixtime INT NOT NULL
,value INT NOT NULL
,PRIMARY KEY(group_id,unixtime)
);

INSERT INTO my_table VALUES
(1 ,  1522540800 ,    866),
(1 ,  1525132800 ,    123),
(1 ,  1527811200 ,    100),
(1 ,  1530403200 ,     85),
(1 ,  1533081600 ,     77),
(1 ,  1535760000 ,     65),
(2 ,  1522540800 ,    376),
(2 ,  1525132800 ,     66),
(2 ,  1527811200 ,     45),
(2 ,  1530403200 ,     58),
(2 ,  1533081600 ,     42),
(3 ,  1522540800 ,    481),
(3 ,  1525132800 ,     68),
(3 ,  1527811200 ,     77),
(3 ,  1530403200 ,     50),
(4 ,  1522540800 ,    792),
(4 ,  1525132800 ,    126),
(4 ,  1527811200 ,     84),
(5 ,  1522540800 ,   1297),
(5 ,  1525132800 ,    203),
(6 ,  1522540800 ,    882);

SELECT x.*
     , value/CASE WHEN @prev = group_id THEN @val:=@val ELSE @val:=value END val
     , @prev:=group_id 
  FROM my_table x
     ,(SELECT @prev:=null,@val:=0) vars 
 ORDER 
    BY group_id
     , unixtime;
+----------+------------+-------+--------+-----------------+
| group_id | unixtime   | value | val    | @prev:=group_id |
+----------+------------+-------+--------+-----------------+
|        1 | 1522540800 |   866 | 1.0000 |               1 |
|        1 | 1525132800 |   123 | 0.1420 |               1 |
|        1 | 1527811200 |   100 | 0.1155 |               1 |
|        1 | 1530403200 |    85 | 0.0982 |               1 |
|        1 | 1533081600 |    77 | 0.0889 |               1 |
|        1 | 1535760000 |    65 | 0.0751 |               1 |
|        2 | 1522540800 |   376 | 1.0000 |               2 |
|        2 | 1525132800 |    66 | 0.1755 |               2 |
|        2 | 1527811200 |    45 | 0.1197 |               2 |
|        2 | 1530403200 |    58 | 0.1543 |               2 |
|        2 | 1533081600 |    42 | 0.1117 |               2 |
|        3 | 1522540800 |   481 | 1.0000 |               3 |
|        3 | 1525132800 |    68 | 0.1414 |               3 |
|        3 | 1527811200 |    77 | 0.1601 |               3 |
|        3 | 1530403200 |    50 | 0.1040 |               3 |
|        4 | 1522540800 |   792 | 1.0000 |               4 |
|        4 | 1525132800 |   126 | 0.1591 |               4 |
|        4 | 1527811200 |    84 | 0.1061 |               4 |
|        5 | 1522540800 |  1297 | 1.0000 |               5 |
|        5 | 1525132800 |   203 | 0.1565 |               5 |
|        6 | 1522540800 |   882 | 1.0000 |               6 |
+----------+------------+-------+--------+-----------------+
21 rows in set (0.00 sec)

Return all group values divided by first value in group

2 Answers2