sql—如何按分区为所有行显示相同的值？

mwg9r5ms 于 2021-07-12 发布在 Spark

关注(0)|答案(3)|浏览(240)

我有一个所有消费者购买的清单，其中一些消费者在时间范围内进行了多次购买。我想用每个消费者第一次购买的位置填充一列，但我得到了以下错误：

Error in SQL statement: ParseException: 
mismatched input '(' expecting <EOF>(line 2, pos 25)

== SQL ==
SELECT consumer_id
       ,location OVER(partition BY table.consumer_id) AS first_purchase_site
---------------------^^^
FROM table

为清楚起见，我的问题是：

SELECT consumer_id
       ,location OVER(partition BY table.consumer_id) AS first_purchase_site
FROM table
WHERE consumer_purchase_order_sequence = 1

sql Hive apache-spark partition-by Alias

来源：https://stackoverflow.com/questions/66482090/how-to-partition-by-to-show-the-same-value-for-all-rows

3条答案

按热度按时间

5m1hhzi41#

我想用每个消费者第一次购买的位置填充一列
你在找什么 first_value() ?

SELECT consumer_id,
       FIRST_VALUE(location) OVER (partition BY table.consumer_id) AS first_purchase_site
FROM table;

你的窗口函数，呃，缺少这个函数。

赞(0）回复(0）举报 2021-07-12

elcex8rz2#

你需要窗口功能 FIRST_VALUE() :

SELECT DISTINCT consumer_id,
       FIRST_VALUE(location) OVER(PARTITION BY consumer_id ORDER BY consumer_purchase_order_sequence) AS first_purchase_site
FROM table

改变 consumer_purchase_order_sequence 与订购采购的列。

赞(0）回复(0）举报 2021-07-12

dojqjjoe3#

很难用窗口计算。你可以用连接，

SELECT 
  table.consumer_id,
  table.location,
  a.first_purchase_site
FROM table LEFT JOIN
  (SELECT consumer_id,location AS first_purchase_site FROM table WHERE 
  consumer_purchase_order_sequence = 1) a ON a.consumer_id=table.consumer_id

赞(0）回复(0）举报 2021-07-12

我来回答

sql—如何按分区为所有行显示相同的值？

3条答案

相关问题

热门标签

最新问答