仅选择最早的日期(BigQuery)

Question

我只想选择最早的日期。使用Max/Min不起作用，因为它是在行级别的，而且我想不出使用over或NTH的方法，因为这个查询每天都会使用不同数量的服务器w_id和z_id运行。

以下查询：

select server, w_id, z_id, date(datetime) as day
from( SELECT server, w_id, datetime, demand.b_id as id, demand.c_type, z_id,
    FROM TABLE_DATE_RANGE(v3_data.v3_,DATE_ADD(CURRENT_DATE(),-2,"day"),
           DATE_ADD(CURRENT_DATE(),-1,"day"))
    where demand.b_id is not null and  demand.c_type = 'rtb'
    group by 1,2,3,4,5,6
    having datetime >=  DATE_ADD(CURRENT_DATE(),-2,"day")
)
group by 1,2,3,4
having count(day)<2
order by z_id, day

给出结果：

Row server     w_id  z_id     day
1     A         722  1837  2016-04-19
2     SPORTS     51  2534  2016-04-19
3     A        1002  2546  2016-04-18
4     A        1303  3226  2016-04-19
5     A        1677  4369  2016-04-18
6     NEW     13608  9370  2016-04-19

所以从上面我只想要2016-04-18。

Answer 1

我认为GROUP_CONCT可以很简单地在这里完成任务：

SELECT
  server,
  w_id,
  z_id,
  day,
FROM (
  SELECT
    server,
    w_id,
    z_id,
    GROUP_CONCAT(day) day,
  FROM (
    SELECT
      server,
      w_id,
      DATE(datetime) day,
      demand.b_id AS id,
      demand.c_type,
      z_id,
    FROM
      TABLE_DATE_RANGE(v3_data.v3_,DATE_ADD(CURRENT_DATE(),-2,"day"), DATE_ADD(CURRENT_DATE(),-1,"day"))
    WHERE
      demand.b_id IS NOT NULL
      AND demand.c_type = 'rtb'
      AND DATE(datetime) >= DATE(DATE_ADD(CURRENT_DATE(),-2,"day"))
    GROUP BY
      1,2,3,4,5,6
    ORDER BY
      day) # Critical to order this dimension to make the GROUP_CONCAT permutations unique
  GROUP BY
    server,
    w_id,
    z_id,
    # day is aggregated in GROUP_CONCAT and so it does not get included in the GROUP BY
    )
WHERE
  day = DATE(DATE_ADD(CURRENT_DATE(),-2,"day"))

Answer 2

大多数内部选择都是未经修改的原始选择剩下的是负责min_day的包装器没有经过测试-就像在路上做的那样-但至少应该给你一个想法

SELECT server, w_id, z_id, [day]
FROM (
  SELECT server, w_id, z_id, [day], MIN([day]) OVER() AS min_day               
  FROM (                                                  
    SELECT server, w_id, z_id, DATE(datetime) AS [day]
    FROM ( 
      SELECT server, w_id, datetime, demand.b_id AS id, demand.c_type, z_id,
      FROM TABLE_DATE_RANGE(v3_data.v3_,DATE_ADD(CURRENT_DATE(),-2,"day"), DATE_ADD(CURRENT_DATE(),-1,"day"))
      WHERE demand.b_id IS NOT NULL AND demand.c_type = 'rtb'
      GROUP BY 1,2,3,4,5,6
      HAVING datetime >= DATE_ADD(CURRENT_DATE(),-2,"day") 
    )
    GROUP BY 1,2,3,4
    HAVING COUNT([day])<2
  )                                                       
)                                                       
WHERE [day] = min_day                                     
ORDER BY z_id, [day]

Answer 3

这两种解决方案都很有帮助，但我相信它们都没有达到我想要的效果，下面的解决方案做到了：

select server, w_id, id, demand.c_type,z_id,
NTH(1, day) First, NTH(2, day) Second, 
from(
SELECT
      server,
      w_id,
      DATE(datetime) as day,
      demand.b_id AS id,
      demand.c_type,
      z_id,
FROM
      TABLE_DATE_RANGE([black-beach-789:v3_data.v3_],DATE_ADD(CURRENT_DATE(),-2,"day"), DATE_ADD(CURRENT_DATE(),-1,"day"))
WHERE
      demand.b_id IS NOT NULL
AND demand.c_type = 'rtb'
AND DATE(datetime) >= DATE(DATE_ADD(CURRENT_DATE(),-2,"day"))
GROUP BY
      1,2,3,4,5,6
order by day
      )
group by 1,2,3,4,5
having first = date(DATE_ADD(CURRENT_DATE(),-2,"day")) and Second is null