SQL - 获取多计数与标准

Question

我有一个客户，客户回访存储到存储三个表格，并存储评论：

客户
ID
出生日期...等。

CustomerVisits
CUSTOMER_ID
STORE_ID
VisitDate

评论
STORE_ID
CUSTOMER_ID
评级

我需要什么样的（希望）得到单个SQL语句是每个商店所有时间访问者的数量，每个商店最近30天内访问者的数量，每个商店的平均客户年龄以及每个商店的平均评估分数。我需要使用一个IN子句，例如Store_ID IN（1,2,3），一次能够为多个商店做到这一点。我知道我可以创建一个临时表并通过store_ids循环，运行多个选择，但宁可在单个选择中执行此操作（如果可能的话）。

在此先感谢！

Answer 1

你可以在子查询如下执行每个计数：

SELECT Stores.Store_ID, 
     review.AvgRating, 
     cv.VisitsLast20days, 
     cv.TotalVisits, 
     cv.AvgCustomerAge 
FROM Stores 
     LEFT JOIN 
     ( SELECT Store_ID, [AvgRating] = AVG(Rating) 
      FROM Reviews 
      GROUP BY Store_ID 
     ) review 
      ON review.Store_ID = Stores.Store_ID 
     LEFT JOIN 
     ( SELECT CustomerVisits.Store_ID, 
        [VisitsLast30Days] = COUNT(CASE WHEN CustomerVisits.VisitDate >= DATEADD(DAY, -30, CURRENT_TIMESTAMP) THEN 1 END), 
        [TotalVisits] = COUNT(*), 
        [AvgCustomerAge] = AVG(DATEDIFF(DAY, Customer.BirthDate, CURRENT_TIMESTAMP))/365.25 
      FROM CustomerVisits 
        INNER JOIN Customer 
         ON Customer.Customer_ID = CustomerVisits.Customer_ID 
      GROUP BY CustomerVisits.Store_ID 
     ) cv 
      ON cv.Store_ID = Stores.Store_ID; 

我假设你有一个表叫卖场要做到这一点，并使用LEFT JOIN的上，并非每个店都有访问或假设回顾。

我也使用了一种相当粗略的方法来计算一个客户的平均年龄，但由于它只是一个平均值，并且实际上并没有为一个人计算出准确的年龄，我怀疑它会对结果产生不利影响

Answer 2

尝试：

select s.Store_ID, 
     count(distinct v.Customer_ID) all_time_visitors, 
     count(distinct case when datediff(d, v.VisitDate, getdate()) <= 30 then v.Customer_ID end) 30day_visitors, 
     avg(datediff(yy, c.BirthDate, getdate())) avg_customer_age, 
     max(r.avg_rating) avg_rating 
from Stores s 
left join CustomerVisits v on s.Store_ID = v.Store_ID 
left join Customers c on v.Customer_ID = c.Customer_ID 
left join (select Store_ID, avg(Rating) avg_rating 
      from Reviews 
      group by Store_ID) r on s.Store_ID = r.Store_ID 
where s.Store_ID in (1,2,3) /*amend as required*/ 
group by s.Store_ID