sql - RedShift 中的递归查询
问题描述
我对 SQL 和 RedShift 也很陌生。我有两张桌子。
account_usage:
account_id | usage_month | usage_cost | usage_plan | usage_type
1 | 06-01-2018 | 100$ | 2018 - Custom | dining
1 | 06-01-2018 | 40$ | 2018 - Standard | office_supply
2 | 06-01-2018 | 20$ | 2018 - Standard | dining
2 | 06-01-2018 | 30$ | 2018 - Custom | office_supply
3 | 06-01-2018 | 25$ | 2018 - Custom | dining
3 | 06-01-2018 | 22$ | 2018 - Standard | office_supply
account_structure:
account_id | account_parent_id | account_name
1 | 3 | account_1
2 | 3 | account_2
3 | 0 | account_3
从这两个表中,我想构建一个聚合表。在此表中,每个 Id 的总使用量将是同一帐户的使用量 + 其所有子帐户的使用量之和。total_usage_by_type 将是一个 json 字符串,它将按 json 字符串中的 usage_type 累积使用情况。
account_usage_aggregations:
account_id | usage_month | usage_plan | total_usage | total_usage_by_type
1 | 06-01-2018 | 2018-Custom | 100 |{"dining":100}
1 | 06-01-2018 | 2018-Standard | 40 |{"office_supply":40}
2 | 06-01-2018 | 2018-Custom | 30 |{"office_supply":30}
2 | 06-01-2018 | 2018-Standard | 20 |{"dining":20}
3 | 06-01-2018 | 2018-Standard | 82 |{"office_supply":62 , "dining": 20}
3 | 06-01-2018 | 2018-Custom | 155 |{"office_supply":100, "dining": 55}
我想在递归查询中解决这个问题并从这个开始
with C as (
select account_id,
usage_type,
usage_cost,
account_id as RootID
from account_usage
union all
select account_id,
usage_type,
usage_cost,
C.RootID
from account_usage
join C
on account_structure.account_parent_id = C.account_id
) select * from C;
但是我遇到了以下错误。
[Amazon](500310) Invalid operation: relation "c" does not exist;
1 个语句失败。
有没有办法在 redShift 中执行递归查询?
解决方案
Recursive CTEs are now supported in Redshift starting April 29th, 2021 using the WITH RECURSIVE
syntax:
- https://aws.amazon.com/about-aws/whats-new/2021/04/amazon-redshift-announces-support-for-heirarchical-data-queries-with-recursive-cte/
- https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html#r_WITH_clause-recursive-cte
WITH RECURSIVE c AS (
SELECT account_id, usage_type, usage_cost, account_id AS RootID
FROM account_usage
UNION ALL
SELECT account_id, usage_type, usage_cost, c.RootID
FROM account_usage
JOIN c ON account_structure.account_parent_id = c.account_id
)
SELECT *
FROM C;
推荐阅读
- firebase - 如何在flutter中检查文档ID是否存在于集合中
- python - 找到所有可能的二十一点手的算法
- nginx - 将一些 url 重定向到另一个端口
- javascript - 在基数排序中可视化第二次迭代的问题
- flutter - 颤振错误:参数类型“列表”
?不能分配给参数类型“列表” ' - encoding - 更改堆栈交换 api 请求中的编码
- java - 使用 Java 中的 JSON 密钥访问 GCS 并获取方法抛出“com.google.cloud.storage.StorageException”异常
- json - 解析 loadbalancer.yaml 时出错:将 YAML 转换为 JSON 时出错:yaml:第 4 行:找到无法启动任何令牌的字符
- arrays - MPI_GATHERV (Fortran) 从二维子矩阵创建一个新的二维矩阵
- typescript - VSCODE:打字稿。运行测试说我遇到了嵌套问题。无法弄清楚如何解决