sql - SQL- 查询无效,因为聚合函数对每个日期和 ID 进行简单计算
问题描述
我对 SQL 很陌生(不到 100 小时)。问题案例如下所述。每次我尝试查询时,要么得到不正确的输出,要么得到“不包含在聚合函数或 GROUP BY 子句中”的错误。尝试搜索类似的问题或示例但没有结果。我现在迷路了。请帮忙
我有三张桌子
表计算,
Source_id | date(yyyymmdd) | metric1 | metric 2
-------------------------------------------------
1 | 20201010 | 2 | 3
2 | 20201010 | 4 | 5
3 | 20201010 | 6 | 7
1 | 20201011 | 8 | 9
2 | 20201011 | 10 | 11
3 | 20201011 | 12 | 13
1 | 20201012 | 14 | 15
2 | 20201012 | 16 | 17
3 | 20201012 | 18 | 19
表源
Source_id | Description
------------------------
1 | ABC
2 | DEF
3 | XYZ
表因子
Date | Factor
-----------------
20201010 | .3
20201011 | .5
20201012 | .7
如果用户选择的日期是 20201010 到 20201012 那么结果将是
所需结果
Source_id | Calculated Value
-------------------------------------------------------------------------------
ABC | (((2x3)x.3 + (8x9)x.5 + (14x15)x.7))/(No of dates selected in this case =3)
DEF | (((4x5)x.3+ (10x11)x.5 + (16x17)x.7))/(No of dates selected in this case =3)
XYZ | (((6x7)x.3+ (12x13)x.5 + (18x19)x.7))/(No of dates selected in this case =3)
日期将是用户定义的输入,因此计算的值应该是许多日期的平均值。所选日期将始终在范围内定义,而不是随机多选。
在表 calc 中,source_id 和 date 一起将是唯一的。每个日期都有要乘以该日期的所有 source_id 的因子。
如果用户选择的日期是从 20201010 到 20201011,那么结果将是
Source_id | Calculated Value
-------------------------------------------------------------------------------
ABC | ((2x3)x.3+(8x9)x.5)/2
DEF | ((4x5)x.3+(10x11)x.5)/2
XYZ | ((6x7)x.3+(12x13)x.5)/2
如果用户选择的日期是 20201012 那么结果将是
Source_id | Calculated Value
-------------------------------------------------------------------------------
ABC | (14x15)x.7
DEF | (16x17)x.7
XYZ | (18x19)x.7
解决方案
创建一个 CTE 来存储开始和结束日期并将其交叉连接到表的连接中,按源分组和聚合:
WITH cte AS (SELECT '20201010' min_date, '20201012' max_date)
SELECT s.Description,
ROUND(SUM(c.metric1 * c.metric2 * f.factor / (DATEDIFF(day, t.min_date, t.max_date) + 1)), 2) calculated_value
FROM cte t CROSS JOIN Source s
LEFT JOIN Calc c ON c.Source_id = s.Source_id AND c.date BETWEEN t.min_date AND t.max_date
LEFT JOIN Factor f ON f.date = c.date
GROUP BY s.Source_id, s.Description
请参阅演示。
结果:
> Description | calculated_value
> :---------- | ---------------:
> ABC | 61.60
> DEF | 83.80
> XYZ | 110.00
推荐阅读
- java - 如何在 Spring Boot 上返回 JSONObject?
- soap - node-soap 将命名空间添加到信封
- c++ - 当我尝试在 C++ 中使用 SDL_RenderCopy 复制纹理时出现错误
- facebook - React Native Facebook SDK ShareDialog 要求再次登录
- java - 如何将我的 JavaFX 图形放入 JFrame?
- xml -
- php - PHPMailer - 向存储在 mySQL 数据库中的地址发送多封电子邮件
- database - 用于在 GB 规模(流式和静态)数据集上查询和警报的技术堆栈
- python - 如何生成 N 个整数(比如 365),总和为某个值 K(比如 20)?
- python - python 浮点数列表,总和为 1.0,但 (sum(list) == 1.0) 给出 False。为什么?