I would like to group both by day (my times are in unix epoch) and name that gives me the score times the multiplier.

My table looks like this:

time             | name    | score | multiplier
1646733600000000 | Albert  | 10    | 1
1646744400000000 | John    | 5     | 1.5
1646780400000000 | John    | 3     | 2.2
1646812800000000 | Albert  | 7     | 1.1
1646816400000000 | Marie   | 26    | 0.5
1646852400000000 | Marie   | 1     | 2.2
1646870400000000 | Georgie | 30    | 0.75
1646924400000000 | Georgie | 2     | 0.5
1646938800000000 | Kevin   | 15    | 0.3

The result would be:

day        | name    | score * multiplier
2022-03-08 | Albert  | 10 * 1
2022-03-08 | John    | (5 * 1.5)   (3 * 2.2)
2022-03-09 | Albert  | 7 * 1.1
2022-03-09 | Marie   | (26 * 0.5)   (1 * 2.2)
2022-03-10 | Georgie | (30 * 0.75)   (2 * 0.5)
2022-03-10 | Kevin   | 15 * 0.3

CodePudding user response：

This should be simple enough - main trick to divide timestamp by 1000000 to have it in seconds and not microseconds:

-- sample data
WITH dataset (time, name, score, multiplier) AS (
    VALUES
    (1646733600000000,  'Albert', 10,    1),
    (1646744400000000,  'John', 5 ,    1.5),
    (1646780400000000,  'John', 3 ,    2.2),
    (1646812800000000,  'Albert', 7 ,    1.1),
    (1646816400000000,  'Marie', 26,    0.5),
    (1646852400000000,  'Marie', 1 ,    2.2),
    (1646870400000000,  'Georgie', 30,    0.75),
    (1646924400000000,  'Georgie', 2 ,    0.5),
    (1646938800000000,  'Kevin', 15,    0.3)
) 

--query
select date(from_unixtime(time/1000000)) day, name, sum(score*multiplier) "score * multiplier" 
from dataset
group by 1, name -- or date(from_unixtime(time/1000000)) instead of 1
order by 1, name -- for output order

Output: