是的,AnalyticDB PostgreSQL版有内置的函数可以方便地计算同比、环比等指标。
例如,ADB-PG 提供了用于环比计算的 LAG和LEAD 函数,用于同比计算的 DATE_TRUNC 函数和DATE_PART 函数。
以下是一些示例查询,演示如何使用这些函数来计算同比和环比:
- 计算月销售额的环比增长率:
SELECT
(m1.total_sales - m2.total_sales) / m2.total_sales AS mom_growth_rate
FROM
(
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(order_amount) AS total_sales
FROM
sales_table
WHERE
order_date BETWEEN '2019-01-01' AND '2020-12-31'
GROUP BY
1
) AS m1
JOIN
(
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(order_amount) AS total_sales
FROM
sales_table
WHERE
order_date BETWEEN '2019-01-01' AND '2020-12-31'
GROUP BY
1
) AS m2
ON m1.month = m2.month - INTERVAL '1 month';
- 计算月销售额的同比增长率:
SELECT
(m1.total_sales - m2.total_sales) / m2.total_sales AS yoy_growth_rate
FROM
(
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(order_amount) AS total_sales
FROM
sales_table
WHERE
order_date BETWEEN '2020-01-01' AND '2020-12-31'
GROUP BY
1
) AS m1
JOIN
(
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(order_amount) AS total_sales
FROM
sales_table
WHERE
order_date BETWEEN '2019-01-01' AND '2019-12-31'
GROUP BY
1
) AS m2
ON DATE_PART('month', m1.month) = DATE_PART('month', m2.month)
以上仅是示例查询,您可以根据实际的业务需求进行调整和优化。