apache-spark - Databricks 和 Spark 中的公用表表达式 (CTE)
问题描述
我在 Databricks 中有一个 spark 数据框。我正在尝试使用公用表表达式 (CTE) 运行一些 sql 查询。这是前 10 行数据
+----------+----------+------+---+---+---------+-----------------+
| data_date| user_id|region|sex|age|age_group|sum(duration_min)|
+----------+----------+------+---+---+---------+-----------------+
|2020-01-01|22600560aa| 1| 1| 28| 2| 0.0|
|2020-01-01|17148900ab| 6| 2| 60| 5| 1138.0|
|2020-01-01|21900230aa| 5| 1| 43| 4| 0.0|
|2020-01-01|35900050ac| 8| 1| 16| 1| 224.0|
|2020-01-01|22300280ad| 6| 2| 44| 4| 8.0|
|2020-01-02|19702160ac| 2| 2| 55| 5| 0.0|
|2020-02-02|17900020aa| 5| 2| 64| 5| 264.0|
|2020-02-02|16900120aa| 3| 1| 69| 6| 0.0|
|2020-02-02|11160900aa| 6| 2| 52| 5| 0.0|
|2020-03-02|16900290aa| 5| 1| 37| 3| 0.0|
+----------+----------+------+---+---+---------+-----------------+
在这里,我将每个用户注册日期存储在 regs CTE 中,然后计算每月的注册数量。这个带有 CTE 的块在 Databricks 中没有任何问题
%sql
WITH regs AS (
SELECT
user_id,
MIN(data_date) AS reg_date
FROM df2
GROUP BY user_id)
SELECT
month(reg_date) AS reg_month,
COUNT(DISTINCT user_id) AS users
FROM regs
GROUP BY reg_month
ORDER BY reg_month ASC;
但是,当我在之前的 sql 查询中添加另一个 CTE 时,它会返回一个错误(我在 sql server 中测试了这个块,它工作正常)。我无法弄清楚为什么不在 spark databricks 中工作。
%sql
WITH regs AS (
SELECT
user_id,
MIN(data_date) AS reg_date
FROM df2
GROUP BY user_id
),
regs_per_month AS (
SELECT
month(reg_date) AS reg_month,
COUNT(DISTINCT user_id) AS users
FROM regs
GROUP BY reg_month
)
SELECT
reg_month,
users,
LAG(users, 1) OVER (ORDER BY regs_per_month ASC) AS previous_users
FROM regs_per_month
ORDER BY reg_month ASC;
这是错误消息
Error in SQL statement: AnalysisException: cannot resolve '`regs_per_month`' given input columns: [regs_per_month.reg_month, regs_per_month.users]; line 20 pos 31;
'Sort ['reg_month ASC NULLS FIRST], true
解决方案
您可以简单地使用逗号在 Spark SQL 中嵌套公用表表达式 (CTE),例如
%sql
;WITH regs AS (
SELECT
user_id,
MIN(data_date) AS reg_date
FROM df2
GROUP BY user_id
),
regs_per_month AS (
SELECT
month(reg_date) AS reg_month,
COUNT(DISTINCT user_id) AS users
FROM regs
GROUP BY reg_month
)
SELECT
reg_month,
users,
LAG(users, 1) OVER (ORDER BY reg_month ASC) AS previous_users
FROM regs_per_month
ORDER BY reg_month ASC;
如前所述,您的LAG
声明应引用该reg_month
列而不是regs_per_month
CTE。
作为嵌套 CTE 的另一种方法,您可以使用多个WITH
语句,例如
%sql
;WITH regs_per_month AS (
WITH regs AS (
SELECT
user_id,
MIN(data_date) AS reg_date
FROM df2
GROUP BY user_id
)
SELECT
month(reg_date) AS reg_month,
COUNT(DISTINCT user_id) AS users
FROM regs
GROUP BY reg_month
)
SELECT
reg_month,
users,
LAG( users, 1 ) OVER ( ORDER BY reg_month ASC ) AS previous_users
FROM regs_per_month
ORDER BY reg_month ASC;
推荐阅读
- xml - Excel、XML 和 XSD
- java - 在Ubuntu中连接到服务器套接字时连接超时?
- sql - 只有字母和数字的字符串可以注入 SQL 语句
- typescript - “typeof Validators”类型上不存在属性“min”
- netlogo - 如何测试死亡率是否正确
- android - 如何将 iPhone 添加到 Android Studio?
- visual-studio - 为什么VS2017中我的C++-Project中的“添加引用”列表是空的?
- node-red - Node-Red UI 模板:如何“修复”变量的值?
- django - 如何在 django 模型中使用外键获取对象的所有属性
- forms - 将文件直接发送到 Google Drive 的多文件上传表单