白天分组，presto的另一个字段

Question

我想按天(我的时间在unix epoch中(和给我分数乘以乘数的名称进行分组。

我的桌子是这样的：

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

结果是：

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

Answer 1

这应该足够简单-主要的技巧是将时间戳除以1000000，使其以秒而不是微秒为单位：

-- 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

输出：

10.014.100000000000001//tr>23.54.5//tr>

天		名称	分数*乘数
2022-03-08	阿尔伯特
2022-03-08	John
2022-03-09	Albert	7.700000000000001
2022-03-09	玛丽	15.2
2022-03-10	Georgie
2022-03-10	Kevin