如何在Zoho Analytics中实现合作伙伴周/月维度的订单营收排名环比

要实现按订单数、营收的周/月环比排名，核心是先按时间周期（周/月）计算每个合作伙伴的业绩数据，再分别生成对应周期的排名，最后将不同周期的排名横向整合为你需要的表格格式。以下是具体实现步骤和代码：

一、周环比排名实现（订单数维度） #

步骤1：计算各周的订单数及对应排名 #

先通过CTE计算每个合作伙伴每周的订单量，以及该周内的排名：

WITH weekly_order_data AS (
    SELECT
        co.id AS org_id,
        co.name AS Customer_Organization,
        DATE_TRUNC('week', o.created_at) AS week_start, -- 按周截断日期，获取周起始时间
        COUNT(o.id) AS order_count,
        RANK() OVER (PARTITION BY DATE_TRUNC('week', o.created_at) ORDER BY COUNT(o.id) DESC) AS weekly_rank
    FROM orders o
    JOIN customer_organizations co ON o.customer_organization_id = co.id
    WHERE co.status = 'enabled' 
      AND co.customer_organization_type = 'partner'
    GROUP BY co.id, co.name, DATE_TRUNC('week', o.created_at)
)

步骤2：横向拼接当前周与上周的排名 #

使用条件聚合将相邻两周的排名合并到同一行：

SELECT
    Customer_Organization,
    MAX(CASE WHEN week_start = CURRENT_DATE - INTERVAL '1 week' THEN weekly_rank END) AS "Week 1 (上周)",
    MAX(CASE WHEN week_start = CURRENT_DATE THEN weekly_rank END) AS "Week 2 (当前周)"
FROM weekly_order_data
WHERE week_start IN (CURRENT_DATE, CURRENT_DATE - INTERVAL '1 week')
GROUP BY Customer_Organization
ORDER BY "Week 2 (当前周)";

二、月环比排名实现（订单数维度） #

只需将周相关的日期函数替换为月维度即可：

WITH monthly_order_data AS (
    SELECT
        co.id AS org_id,
        co.name AS Customer_Organization,
        DATE_TRUNC('month', o.created_at) AS month_start,
        COUNT(o.id) AS order_count,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', o.created_at) ORDER BY COUNT(o.id) DESC) AS monthly_rank
    FROM orders o
    JOIN customer_organizations co ON o.customer_organization_id = co.id
    WHERE co.status = 'enabled' 
      AND co.customer_organization_type = 'partner'
    GROUP BY co.id, co.name, DATE_TRUNC('month', o.created_at)
)
SELECT
    Customer_Organization,
    MAX(CASE WHEN month_start = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') THEN monthly_rank END) AS "Month 1 (上月)",
    MAX(CASE WHEN month_start = DATE_TRUNC('month', CURRENT_DATE) THEN monthly_rank END) AS "Month 2 (当前月)"
FROM monthly_order_data
WHERE month_start IN (DATE_TRUNC('month', CURRENT_DATE), DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month'))
GROUP BY Customer_Organization
ORDER BY "Month 2 (当前月)";

三、营收维度的环比排名实现 #

如果要按营收计算排名，只需将COUNT(o.id)替换为SUM(o.revenue)（假设营收字段为revenue），调整排名的排序依据即可，以周为例：

WITH weekly_revenue_data AS (
    SELECT
        co.id AS org_id,
        co.name AS Customer_Organization,
        DATE_TRUNC('week', o.created_at) AS week_start,
        SUM(o.revenue) AS total_revenue,
        RANK() OVER (PARTITION BY DATE_TRUNC('week', o.created_at) ORDER BY SUM(o.revenue) DESC) AS revenue_rank
    FROM orders o
    JOIN customer_organizations co ON o.customer_organization_id = co.id
    WHERE co.status = 'enabled' 
      AND co.customer_organization_type = 'partner'
    GROUP BY co.id, co.name, DATE_TRUNC('week', o.created_at)
)
SELECT
    Customer_Organization,
    MAX(CASE WHEN week_start = CURRENT_DATE - INTERVAL '1 week' THEN revenue_rank END) AS "Week 1 (上周营收排名)",
    MAX(CASE WHEN week_start = CURRENT_DATE THEN revenue_rank END) AS "Week 2 (当前周营收排名)"
FROM weekly_revenue_data
WHERE week_start IN (CURRENT_DATE, CURRENT_DATE - INTERVAL '1 week')
GROUP BY Customer_Organization
ORDER BY "Week 2 (当前周营收排名)";

关键说明 #

• DATE_TRUNC是Zoho Analytics支持的日期截断函数，用于将日期统一到周/月的起始时间，确保同一周期的数据被正确分组。
• PARTITION BY子句确保排名是在每个时间周期内独立计算的，不会跨周期排名。
• 条件聚合（CASE WHEN）用于将纵向的周期数据转为横向列，匹配你需要的表格格式。
• 如果你的订单表日期字段不是created_at，请替换为实际的订单日期字段名；营收字段同理。

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