mysql - 如何将 MIN(date) 函数集成到 SELECT*
问题描述
我试图解决的问题的细分。我需要:
最早
created_date
获取每个user_id
. 我知道这可以通过以下查询来完成:SELECT user_id, MIN(created_date) FROM new_schema.transactions GROUP BY user_id
将上面的查询集成到下面的查询中。下面的工作正常。
SELECT *, t.amount / POWER(10, cd.exponent) * fx.rate AS usd_equivalent FROM new_schema.transactions t JOIN new_schema.fx_rates fx ON t.currency = fx.ccy JOIN new_schema.currency_details cd ON t.currency = cd.currency WHERE fx.base_ccy = "USD" AND t.state = "COMPLETED" AND t.type = "CARD_PAYMENT" HAVING usd_equivalent > 10;
基本上,除了我在第二个查询中执行的操作之外,我还需要在应用上述操作后created_date
每次都获取最早的记录。user_id
如何丰富我当前的查询以促进这一点?
解决方案
您可以使用窗口函数:
SELECT *, t.amount / POWER(10, cd.exponent) * fx.rate AS usd_equivalent
FROM (SELECT t.*,
MIN(created_date) OVER (PARTITION BY user_id) as first_created_date
FROM new_schema.transactions t
) t JOIN
new_schema.fx_rates fx
ON t.currency = fx.ccy JOIN
new_schema.currency_details cd
ON t.currency = cd.currency
WHERE fx.base_ccy = 'USD' AND
t.state = 'COMPLETED' AND
t.type = 'CARD_PAYMENT'
HAVING usd_equivalent > 10;
推荐阅读
- wordpress - 如何使 wordpress 重写规则在子目录下工作?
- android - MotionLayout - OnSwipe 不适用于可点击的子项
- google-bigquery - 使用 DataFlow 将不同方案的多个文件加载到 BigQuery
- r - Spotfire/TERR 问题 - 解析错误(文本 = 脚本)
- swiftui - 此列表不滚动的任何明显原因?
- python - I want to build my own "explode" function, but I have some problem
- reactjs - ReactJs problem with route only in production server
- java - FirebaseUser - Android - 获取登录提供程序
- julia - 在 Julia 中使用变量后 for 循环显着变慢
- haskell - No instance for (Integral Double) arising from a use of ‘floor’