sql - 计算每行的第一个和第二个最大值以及两个雪花 SQL 的平均值

Question

我有一个具有以下架构的表：

uid	访问名称	参观日期	销售数量
xyz	访问 1	2020-01-01	29
xyz	访问 2	2020-01-03	250
xyz	访问 3	2020-01-04	20
xyz	访问 4	2020-01-27	21
美国广播公司	访问 1	2020-02-01	29
美国广播公司	访问 2	2020-03-03	34
美国广播公司	访问 3	2020-04-04	35
美国广播公司	访问 4	2020-04-27	41

基表销售

每个唯一 id 都有一些唯一访问，每个唯一 id 重复，每次访问时，我必须计算每个用户的两个最高销售量 - 在他们之前的访问（升序）直到行中命名的当前访问对于每个唯一 id 并且不包括当前行。

输出将是 - 同一张表加上这些列

最大销售	第二次最大销售	两个最大销售额的平均值

输出表

我已经使用窗口函数来获得最大值，但我正在努力为每一行的每个用户获得第二高的销售额。这可以使用sql吗？如果是这样，脚本会是什么样子？

标签： sqlsnowflake-cloud-data-platform

Answer 1

更新：我重新写了我的答案，因为前一个忽略了某些要求。

要跟踪前两个最高值，您可以在 JS 中编写一个 UDTF 来保持该排名：

create or replace function udtf_top2_before(points float)
returns table (output_col array)
language javascript
as $$
{
    processRow: function f(row, rowWriter, context){
        rowWriter.writeRow({OUTPUT_COL: this.prevmax.slice().reverse()});
        this.prevmax.push(row.POINTS);
        // silly js sort https://stackoverflow.com/a/21595293/132438
        this.prevmax = this.prevmax.sort(function (a, b) {return a - b;}).slice(-2);
    }        
    , initialize: function(argumentInfo, context) {
        this.prevmax = [];
    }
}
$$;

然后该表格 UDF 可以为您提供预期的数字：

with data as (
    select v:author::string author, v:score::int score, v:subreddit, v:created_utc::timestamp ts
    from reddit_comments_sample
    where v:subreddit = 'wallstreetbets'
)


select author, score, ts
    , output_col[0] prev_max
    , output_col[1] prev_max2
    , (prev_max+ifnull(prev_max2,prev_max))/2 avg
from (
    select author, score, ts, output_col 
    from data, table(udtf_top2_before(score::float) over(partition by author order by ts))
    order by author, ts
    limit 100
)

UDTF 基于我之前的帖子：

• https://towardsdatascience.com/sql-puzzle-optimization-the-udtf-approach-for-a-decay-function-4b4b3cdc8596

---

之前：

您可以使用row_number() over()选择前 2 个，然后使用array_agg():

with data as (
    select v:author author, v:score::int score, v:subreddit, v:created_utc::timestamp ts
    from reddit_comments_sample
    where v:subreddit = 'wallstreetbets'
)

select author, arr[0] max_score, arr[1] max_score_2, (max_score+ifnull(max_score_2,max_score))/2 avg
from (
    select author
        , array_agg(score) within group (order by score::int desc) arr
    from (
        select author, score, ts
        from data
        qualify row_number() over(partition by author order by score desc) <= 2
    )
    group by 1
)
order by 4 desc