可能最短的查询，递归检查的大查询

2024-05-18 • 问答

假设我在Big查询中有一个数据，一个月的数据量高达数百万行。例如：

|---------------------|------------------|
|      date           |     user         |
|---------------------|------------------|
|          01-12-2019 |   xyz            |
|---------------------|------------------|
|          02-12-2019 |   xyz            |
|---------------------|------------------|
|          03-12-2019 |   abc            |
|---------------------|------------------|

现在我要检索按日数据，接下来14天的重复用户计数，即首次访问01-12-2019的用户，然后是在接下来的14天再次访问的重复用户的计数（02-12-2019-15-12-2019）。我想出了使用以下查询来检索相同方法的方法，但需要特定的日期。

 SELECT '2019-12-01' AS visit_date,COUNT(DISTINCT user) AS visitors_count
 FROM `user_data`
 WHERE
 date = '2019-12-01' AND user IN (SELECT user FROM `user_data`
 WHERE date between DATE_ADD('2019-12-01',INTERVAL 1 DAY) AND DATE_ADD('2019-12-01',INTERVAL 
 14 DAY) )
 GROUP BY 1

我可以使用的一种方法是UNION ALL，这肯定不是最佳解决方案，这就是为什么对于这种情况，我愿意养成一些最佳实践的原因。

内联查询在这里应该非常有效：

select 
    date,(
        select count(distinct u1.user) 
        from user_data u1 
        where u1.date 
            between date_add(u.date,interval 1 day) 
            and date_add(u.date,interval 14 day)
    ) visitors_count
from (select distinct date from user_data) u

为了提高性能，您希望在(date,user)上建立索引。

如果您有一个引用表保存该表中可用的日期列表，则可以使用if代替执行select distinct：这也可以加快查询速度。

您可以使用union all和聚合来解决此问题。关键是保持日期进出和出。所以：

with ud as (
      select user,date,1 as inc
      from user_data
      union all
      select user,date_add(date,interval 15 day),-1 as inc
      from user_data
     )
select date,sum(inc) as change_on_day,sum(sum(inc)) over (order by date) as total_on_day
from ud
group by date
order by date;

编辑：

您可以修改上面的内容，以使客户获得 first 肯定的公司，并获得 last ：

with ud as (
      select user,1 as inc
      from (select ud.*,lag(date) over (partition by user order by date) as prev_date
            from user_data ud
           ) ud
      where prev_date is null or prev_date < date_add(date,interval -14 day)
      union all
      select user,-1 as inc
      from (select ud.*,lead(date) over (partition by user order by date) as lead_date
            from user_data ud
           ) ud
      where next_date is null or next_date < date_add(date,interval 14 day)
     )
select date,sum(sum(inc)) over (order by date) as total_on_day
from ud
group by date
order by date;

以下用于BigQuery标准SQL

#standardSQL
SELECT visit_day,COUNT(DISTINCT IF(visits_next_14_days,NULL,user)) AS repeating_visitors_count 
FROM (
  SELECT visit_day,user,0 = COUNT(1) OVER(
    PARTITION BY user 
    ORDER BY UNIX_DATE(visit_day) 
    RANGE BETWEEN 1 FOLLOWING AND 14 FOLLOWING
  ) visits_next_14_days
  FROM `project.dataset.user_data`
)
GROUP BY visit_day

可能最短的查询，递归检查的大查询

pldljf 回答：可能最短的查询，递归检查的大查询

大家都在问