分组依据 在子查询内部

Question

我在Databricks中使用SQL。以下查询有效，但我还想按名为 sale_id 的列进行分组。我该怎么做呢？

%sql
select
  (select
     count(distinct time)
  from
    table
  where
    sign_up > 0)
  /
  (select
     count(distinct time)
   from
     table
   where
    action > 0 or
    click > 0)
    as cc3

Answer 1

使用条件聚合编写查询：

select (count(distinct case when sign_up > 0 then time end) /
        count(distinct case when action > 0 or click > 0 then time end)
       ) as cc3
from table;

那么group by就很容易了：

select col,
       (count(distinct case when sign_up > 0 then time end) /
        count(distinct case when action > 0 or click > 0 then time end)
       ) as cc3
from table
group by col;