Comment écrire ces deux requêtes pour un simple entrepôt de données, à l'aide de la norme ANSI SQL?

Comme indiqué dans le commentaire ci-dessus, je ne comprends pas votre modèle -- donc, ici, est simple pour commencer.

Maintenant, si je veux hebdomadaire des ventes pour l'année civile 2010, les

select 
    CalendarYearWeek
  , sum(SalesAmount)
from factSales as f
join dimDate as d on d.DateKey = f.DateKey
where Year = 2010
group by CalendarYearWeek

CalendarYearWeek est une colonne de dimDate, varchar(8), par exemple "2010-w03', Year est une colonne de type integer dans dimDate trop.

Ne sais pas si c'est proche de ce que vous recherchez, mais peut être un début.

MODIFIER

dimDate a également ces colonnes:

WeekNumberInEpoch, integer -- augmente augmente à partir de quelques époque date dans le passé. Toutes les lignes de dimDate dans la même semaine ont la même WeekNumberInEpoch.

DayOfWeek, de type varchar(10) -- 'dimanche', 'lundi', ...

DayNumberInWeek, integer -- 1-7

Il utilise des expressions de table communes, devrait fonctionner avec la dernière version de PostgreSQL, SQL Server, Oracle, DB2. Pour les autres vous pouvez package les CTE (q_00) dans une sous-requête.

-- for week to previous week
with
q_00 as (
select
WeekNumberInEpoch
, sum(SalesAmount) as Amount
from factSale as f
join dimDate  as d on d.DateKey = f.DateKey
where CalendarYear = 2010
group by WeekNumberInEpoch
)
select
a.WeekNumberInEpoch
, a.Amount as ThisWeekSales
, b.Amount as LastWeekSales
, a.Amount - b.Amount as Difference
from q_00 as a
join q_00 as b on b.WeekNumberInEpoch = a.WeekNumberInEpoch - 1
order by a.WeekNumberInEpoch desc ;
-- for day of week to day of previous week 
-- monday to monday, tuesday to tuesday, ...
with
q_00 as (
select
WeekNumberInEpoch
, DayOfWeek  
, sum(SalesAmount) as Amount
from factSale as f
join dimDate  as d on d.DateKey = f.DateKey
where CalendarYear = 2010
group by WeekNumberInEpoch, DayOfWeek
)
select
a.WeekNumberInEpoch
, a.DayOfWeek  
, a.Amount as ThisWeekSales
, b.Amount as LastWeekSales
, a.Amount - b.Amount as Difference
from q_00 as a
join q_00 as b on (b.WeekNumberInEpoch = a.WeekNumberInEpoch - 1
and b.DayOfWeek = a.DayOfWeek)
order by a.WeekNumberInEpoch desc, a.DayOfWeek ;
-- Sliding by day and day difference (= 7)
with
q_00 as (
select
DayNumberInEpoch
, FullDate
, DayOfWeek
, sum(SalesAmount) as Amount
from factSale as f
join dimDate as d on d.DateKey = f.DateKey
where CalendarYear = 2010
group by DayNumberInEpoch, FullDate, DayOfWeek
)
select
a.FullDate  as ThisDay
, a.DayOfWeek as ThisDayName
, a.Amount    as ThisDaySales
, b.FullDate  as PreviousPeriodDay
, b.DayOfWeek as PreviousDayName
, b.Amount    as PreviousPeriodDaySales
, a.Amount - b.Amount as Difference
from q_00 as a
join q_00 as b on b.DayNumberInEpoch = a.DayNumberInEpoch - 7
order by a.FullDate desc ;

OriginalL'auteur Damir Sudarevic