英文:
SQL clickhouse how to use neighbor func based on value in different column
问题
以下是您的翻译：
我在我的SQL查询中遇到了一个问题。我试图找到“amount”列中当前值与前一个值之间的差异（换句话说，即当前值减去前一个值，也就是delta）。但我需要按“id”分组。以下是我的SQL查询示例以及当前和所需的结果。这里的问题在于当下一行具有不同的“id”时，查询仍然继续查找不同id之间的差异。不清楚如何修复它。有任何想法吗？我使用ClickHouse数据库。
当前：
id 名称 金额 差异
14045254695 苹果 3392.92 3392.92
14045254695 苹果 3408.39 15.47
14045254695 苹果 3451.0 42.61
14045254729 苹果 10.03 -3440.97 <- 这里的问题是脚本做了 10.03 - 3451.0 的计算
14045254729 苹果 1700.0 1689.97 10.03 - 3451.0 的计算
希望如下：
id 名称 金额 差异
14045254695 苹果 3392.92 3392.92
14045254695 苹果 3408.39 15.47
14045254695 苹果 3451.0 42.61
14045254729 苹果 10.0 10.0
14045254729 苹果 1700.0 1690
英文:
I have a problem in my sql query. I try to find difference betweent current and previous value (current value - previous value in other words delta) in column "amount" But i need make it per "id" So here example of my sql query and current and wanted result. Here is a problem that when next row have differnt "id" query is still continue to finding diff between different id's is. Not clear how to fix it. Any ideas? I use clickhouse DB
SELECT
toInt64(visitParamExtractString( (JSONExtractString(message,'id')) ,'id' )) as id,
visitParamExtractString(message ,'name' ) as name,
toFloat64(visitParamExtractString(message, 'amount')) as amount,
round((amount - neighbor(amount, -1)),2) as diff
FROM stage1.log dl
WHERE time >= '2023-04-22 13:01:00'
and time <= '2023-04-22 13:01:30'
and name in ('apple')
and amount > 0
order by time
currently:
id name amount diff
14045254695 apple 3392.92 3392.92
14045254695 apple 3408.39 15.47
14045254695 apple 3451.0 42.61
14045254729 apple 10.03 -3440.97 <- here is problem that script make
14045254729 apple 1700.0 1689.97 10.03 - 3451.0 calculation
wants to be like:
id name amount diff
14045254695 apple 3392.92 3392.92
14045254695 apple 3408.39 15.47
14045254695 apple 3451.0 42.61
14045254729 apple 10.0 10.0
14045254729 apple 1700.0 1690
答案1
得分: 0
看起来你可以使用 lag/leadinframe 和 'partition by' 来处理 id。
https://clickhouse.com/docs/en/sql-reference/window-functions
SELECT
toInt64(visitParamExtractString( (JSONExtractString(message,'id')) ,'id' )) as id,
visitParamExtractString(message ,'name' ) as name,
toFloat64(visitParamExtractString(message, 'amount')) as amount,
round((amount - laginframe(amount, 1) over(partition by id),2) as diff
FROM stage1.log dl
WHERE time >= '2023-04-22 13:01:00'
and time <= '2023-04-22 13:01:30'
and name in ('apple')
and amount > 0
order by time
如果你有任何其他问题，请随时提出。
英文:
seems like you could use lag/leadinframe and 'partition by' the id
https://clickhouse.com/docs/en/sql-reference/window-functions
SELECT
toInt64(visitParamExtractString( (JSONExtractString(message,'id')) ,'id' )) as id,
visitParamExtractString(message ,'name' ) as name,
toFloat64(visitParamExtractString(message, 'amount')) as amount,
round((amount - laginframe(amount, 1) over(partition by id),2) as diff
FROM stage1.log dl
WHERE time >= '2023-04-22 13:01:00'
and time <= '2023-04-22 13:01:30'
and name in ('apple')
and amount > 0
order by time
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论