SQL。在第一个具有历史意义的星期内获得用户名称的收入

Question

我对BigQuery和SQL语句非常陌生，我想执行2次查询。我已经用我自己的方式做了一些查询，但我可以完成这个查询。

我的目标是在相同的sql语句中实现每组名字在头4周的收入(名字应该按第一次约会分组)。

输入

create table Test(id integer, name varchar(100), time_date date, country varchar(100), car varchar(100), amount integer);
insert into Test(id, name, time_date, country, car, amount) values(1, "usuario1", '2020-09-11', 'UK', 'ford', 100);
insert into Test(id, name, time_date, country, car, amount) values(2, "usuario2", '2020-09-01', 'JP', 'ford', 50);
insert into Test(id, name, time_date, country, car, amount) values(3, "usuario1", '2020-07-11', 'UK', 'kia', 300);
insert into Test(id, name, time_date, country, car, amount) values(4, "usuario3", '2020-09-01', 'JP', 'renault', 400);
insert into Test(id, name, time_date, country, car, amount) values(5, "usuario4", '2020-09-11', 'UK', 'renault', 100);
insert into Test(id, name, time_date, country, car, amount) values(6, "usuario5", '2020-09-11', 'UK', 'kia', 50);
insert into Test(id, name, time_date, country, car, amount) values(7, "usuario85", '2020-09-11', 'UK', 'ford', 200);
insert into Test(id, name, time_date, country, car, amount) values(8, "usuario6", '2020-07-11', 'UK', 'ford', 200);
insert into Test(id, name, time_date, country, car, amount) values(9, "usuario2", '2020-07-11', 'UK', 'ford', 200);
insert into Test(id, name, time_date, country, car, amount) values(9, "usuario2", '2020-07-12', 'UK', 'ford', 50);

查询

• 收入来自每一组用户名在他们的头3周。姓名应按其第一次日期分组：

这是我完全可以解决的问题，我认为首先我应该为每个用户计算一个新的变量和第一个日期。在这种情况下，'usuario2‘应该有'2020-07-11’在两行。在此之后，我想我可以计算出“再过3周”，并算出金额的总和。但我真的不知道怎么做。

感谢您花时间阅读这篇文章，任何帮助都将不胜感激。

输出

User - first_date - Revenue 
usuario1 - 2020-07-11 - 300
usuario2 - 2020-07-11 - 250 => (200 + 50)
usuario3 - 2020-09-01 - 400
usuario4 - 2020-09-11 - 100
usuario5 - 2020-09-11 - 50
usuario6 - 2020-07-11 - 200
usuario85 - 2020-09-11 - 200

收入:按第一次约会分组的每一组姓名用户都有其头3周的收入。“Usuario2”是唯一一个在这个范围内又过了一天的人。

Answer 1

嗯嗯。。。使用窗口函数获取最早的时间。然后过滤和聚合：

select name, sum(amount)
from (select t.*,
             min(time_date) over (partition by name) as min_time_date
      from Test t
     ) t
where time_date < date_add(min_time_date, interval 4 * 7 day)
group by name;

这里是一个在Postgres中使用稍微修改过的查询的db<>fiddle。

Answer 2

下面是用于BigQuery标准SQL的

#standardSQL
SELECT name, first_date, SUM(amount) AS Revenue 
FROM (
  SELECT *, MIN(time_date) OVER (PARTITION BY name) AS first_date
  FROM `project.dataset.Test`
) 
WHERE time_date < DATE_ADD(first_date, INTERVAL 3 WEEK)
GROUP BY name, first_date   

如果要应用于问题输出中的样本数据，请参见

Row name        first_date  Revenue  
1   usuario1    2020-07-11  300  
2   usuario2    2020-07-11  250  
3   usuario3    2020-09-01  400  
4   usuario4    2020-09-11  100  
5   usuario5    2020-09-11  50   
6   usuario6    2020-07-11  200  
7   usuario85   2020-09-11  200