统计/page_y中rank值高于/page_x的fullvisitorID数量

统计满足如下条件的fullvisitorID总数量：同一访客在/page_y页面对应的rank数值，高于其在/page_x页面对应的rank数值。

数据集共包含3个字段：

• fullvisitorID：访客唯一标识
• rank：排名值
• page：页面路径

具体记录如下：

1. fullvisitorID：111，rank：1，page：/page_x
2. fullvisitorID：111，rank：2，page：/page_y
3. fullvisitorID：222，rank：1，page：/page_x
4. fullvisitorID：222，rank：2，page：/page_x
5. fullvisitorID：333，rank：2，page：/page_x
6. fullvisitorID：333，rank：1，page：/page_y

统计时首先按fullvisitorID分组，分别提取每个访客对应/page_x、/page_y的rank值，仅当两个页面的rank值都存在、且/page_y的rank值大于/page_x的rank值时，该访客计入统计：

• 访客111：/page_x对应rank=1，/page_y对应rank=2，2>1，符合条件
• 访客222：无/page_y的访问记录，无法对比，不符合条件
• 访客333：/page_y对应rank=1，小于/page_x对应的rank=2，不符合条件

最终符合条件的访客总数为 1。

如果是在数仓中执行统计，可以用行转列的方式先聚合每个访客两个页面的rank值，再做条件过滤，参考代码如下：

SELECT COUNT(DISTINCT fullvisitorID) AS valid_total
FROM (
    SELECT
        fullvisitorID,
        MAX(IF(page = '/page_x', rank, NULL)) AS rank_x,
        MAX(IF(page = '/page_y', rank, NULL)) AS rank_y
    FROM 访问数据表
    GROUP BY fullvisitorID
) tmp
WHERE rank_y > rank_x;

内容的提问来源于stack exchange，提问作者foxchip