>我有一个查询,看起来像
select
number,
class,
unix_timestamp(date1) - unix_timestamp(date2) as time_example,
sum(unix_timestamp(date1) - unix_timestamp(date2)) over(partition by unix_timestamp(date1) - unix_timestamp(date2) order by class) as class_time
from myTable
这给出了诸如
number class time_example class_time
1 math 5 5
1 science 5 10
1 art 5 15
1 math 2 2
1 science 2 4
1 art 2 6
1 math 10 10
1 science 10 20
1 art 10 30
我想根据类添加列,并且只有 3 个不同的列,因为只有 3 列。例如,数学的时间和将得到 17。这是我想要得到的表格
number class class_time
1 math 17
1 science 17
1 art 17
您可以使用group by
select number, class,
sum(unix_timestamp(date1) - unix_timestamp(date2)) as class_time
from myTable
group by number,class;