SQL - Obtenir des comptages multiples avec des critères

Question

J'ai trois tables qui stockent les clients, les visites des clients dans un magasin et les avis sur les magasins :

Clients
ID
Date de naissance...etc.

Visites de clients
Customer_ID
Store_ID
Date de visite

Commentaires
Store_ID
Customer_ID
Evaluation

Ce que j'ai besoin d'obtenir dans une seule instruction SQL (si possible), c'est le nombre de visiteurs de tous les temps par magasin, le nombre de visiteurs des 30 derniers jours par magasin, l'âge moyen des clients par magasin et le score moyen des critiques par magasin. J'ai besoin de pouvoir faire cela pour plusieurs magasins à la fois en utilisant une clause IN comme where Store_ID IN (1,2,3). Je sais que je pourrais créer une table temporaire et boucler à travers les store_ids, en exécutant des sélections multiples, mais je préférerais le faire en une seule sélection si c'est possible.

Merci d'avance !

Answer 1

Vous pouvez effectuer chaque comptage dans une sous-requête comme suit :

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;

J'ai supposé que vous disposiez d'une table appelée "magasins" pour effectuer cette opération et j'ai utilisé des JOINTS DE GAUCHE en partant du principe que tous les magasins ne font pas l'objet d'une visite ou d'un commentaire.

J'ai également utilisé une méthode assez rudimentaire pour calculer l'âge moyen d'un client, mais comme il ne s'agit que d'une moyenne et non d'un âge précis pour un individu, je doute que cela ait un effet négatif sur les résultats.

Answer 2

Essayez :

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