如何将多个SQL聚合查询合并为一个查询
如何将多个SQL聚合查询合并为一个查询
提问于 2018-01-26 19:44:18
我有许多查询，我希望将它们合并到一个查询中，即使它们有一个共同的筛选字段，我也不能完全确定我试图实现的目标是否可行，因为这些都是聚合查询。
declare @salesforceId int
set @salesforceId = 109924
SELECT
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating,
COUNT(*) as ReviewCount FROM QuestionResponse qr
JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
AND sr.StatusId IN (5, 7)
AND qr.QuestionId = 1 --general feedback
AND sr.RestaurantNetworkId = @salesforceId
SELECT
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating,
COUNT(*) as ReviewCount FROM QuestionResponse qr
JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
AND sr.StatusId IN (5, 7)
AND qr.QuestionId = 3 --food rating
AND sr.RestaurantNetworkId = @salesforceId
SELECT
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating,
COUNT(*) as ReviewCount FROM QuestionResponse qr
JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
AND sr.StatusId IN (5, 7)
AND qr.QuestionId = 4 --drinks rating
AND sr.RestaurantNetworkId = @salesforceId
SELECT
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating,
COUNT(*) as ReviewCount FROM QuestionResponse qr
JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
AND sr.StatusId IN (5, 7)
AND qr.QuestionId = 5 -- restaurant ambience
AND sr.RestaurantNetworkId = @salesforceId
SELECT
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating,
COUNT(*) as ReviewCount FROM QuestionResponse qr
JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
AND sr.StatusId IN (5, 7)
AND qr.QuestionId = 6 -- service rating
AND sr.RestaurantNetworkId = @salesforceId
SELECT
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating,
COUNT(*) as ReviewCount FROM QuestionResponse qr
JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
AND sr.StatusId IN (5, 7)
AND qr.QuestionId = 7 -- booking service
AND sr.RestaurantNetworkId = @salesforceId
SELECT
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating,
COUNT(*) as ReviewCount FROM QuestionResponse qr
JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
AND sr.StatusId IN (5, 7)
AND qr.QuestionId = 12 -- Recommend Restaurant
AND sr.RestaurantNetworkId = @salesforceId
SELECT
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating,
COUNT(*) as ReviewCount FROM QuestionResponse qr
JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
AND sr.StatusId IN (5, 7)
AND qr.QuestionId = 13 -- Overall Rating
AND sr.RestaurantNetworkId = @salesforceId
SELECT
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating,
COUNT(*) as ReviewCount FROM QuestionResponse qr
JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
AND sr.StatusId IN (5, 7)
AND qr.QuestionId = 525 -- Value for Money
AND sr.RestaurantNetworkId = @salesforceId
SELECT
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating,
COUNT(*) as ReviewCount FROM QuestionResponse qr
JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
AND sr.StatusId IN (5, 7)
AND qr.QuestionId = 526 -- Location
AND sr.RestaurantNetworkId = @salesforceId
SELECT
count(*) as Total,
CultureInvariantText AS Tag
FROM
SurveyResponse SR
INNER JOIN
[QuestionResponseFixedOptions] QR ON SR.SurveyResponseId = QR.SurveyResponseId
INNER JOIN
QuestionResponseOption QRO ON QR.[ResponseOptionId] = QRO.[ResponseOptionId]
INNER JOIN
Question Q ON QRO.QuestionId = Q.QuestionId
INNER JOIN
LocalizableText LT ON QRO.ResponseValue = LT.LocalizableTextId
where sr.RestaurantNetworkId = @salesforceId
group by CultureInvariantText输出可以是如下内容:所有查询的计数都是相同的。
|GeneralFeedback|FoodRating|....|.....|....|Count|回答 4
Stack Overflow用户
发布于 2018-01-26 19:57:46
如果它们都访问同一个表，则应该执行条件聚合：
AVG(CAST CASE WHEN <condtion> THEN qr.RatingScaleOptionId END as float)您可以在您的SELECT子句中使用任意多个这样的参数。只要确保只保留WHERE子句中的公共条件即可。然后，变化的条件属于CASE的WHEN。
点击此处了解更多信息：https://modern-sql.com/feature/filter
Stack Overflow用户
发布于 2018-01-26 19:50:53
您可以添加一个代理列，以告知行代表哪个聚合，格式化每个查询以生成相同的行，并使用UNION ALL组合它们：
SELECT 'GeneralFeedback' AS Type, AVG(...) AS Rating, COUNT(*) AS ReviewCount
FROM QuestionResponse ...
UNION ALL
SELECT 'FoodRating' AS Type, AVG(...) AS Rating, COUNT(*) AS ReviewCount
FROM QuestionResponse ...
...
UNION ALL
SELECT 'Total' as Type, 0 as Rating, COUNT(*) as ReviewCount
FROM SurveyResponse ...通过添加一个评级为零的列并将Total称为ReviewCount，将'Total'行“硬塞”到公共结构中。执行此查询的代码将需要特殊处理，以便将此行与从查询返回的其他行分开处理。
Stack Overflow用户
发布于 2018-01-26 20:09:08
尝尝这个。请注意，我更改了decimal的浮点数。您将拥有更高的精度，这取决于您所工作的应用程序的类型。您需要根据您的~10个案例来完成查询。
SELECT AVG( CASE WHEN qr.QuestionId = 1 THEN CAST(qr.RatingScaleOptionId as decimal) END as GeneralFeedback_Rating
, COUNT(CASE WHEN qr.QuestionId = 1 THEN 1 END) as GeneralFeedback_Count
, AVG( CASE WHEN qr.QuestionId = 3 THEN CAST(qr.RatingScaleOptionId as decimal) END as Food_Rating
, COUNT(CASE WHEN qr.QuestionId = 3 THEN 1 END) as Food_Count
, AVG( CASE WHEN qr.QuestionId = 4 THEN CAST(qr.RatingScaleOptionId as decimal) END as Drinks_Rating
, COUNT(CASE WHEN qr.QuestionId = 4 THEN 1 END) as Drinks_Count
-- ... ... ...
FROM QuestionResponse qr
JOIN SurveyResponse sr
ON qr.SurveyResponseId = sr.SurveyResponseId
AND sr.StatusId IN (5, 7)
AND sr.RestaurantNetworkId = @salesforceId
AND qr.QuestionId IN (1, 3, 4)--1:general feedback, 3:food, 4:drinks, ETC...页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48460734
复制相关文章
相似问题
腾讯云开发者
