How to write these two queries for a simple data warehouse using ANSI SQL?

Question

I am writing a simple data warehouse that will allow me to query a table to observe periodic (say weekly) changes in the data, as well as changes in data changes (for example, weekly weekly weekly sales).

For simplicity's sake, I will present very simplified (almost trivialized) versions of the tables that I use here. The sales data table is a representation and has the following structure:

CREATE TABLE sales_data (
     sales_time date NOT NULL,
     sales_amt double NOT NULL
)

For the purpose of this question. I left other fields that you expect to see - for example product_id, sales_person_id, etc. Etc., since they are not directly related to this issue. AFAICT, the only fields that will be used in the request are the fields sales_time and sales_amt (unless I am mistaken).

I also have a date size table with the following structure:

CREATE TABLE date_dimension (
  id integer  NOT NULL,
  datestamp   date NOT NULL,
  day_part    integer NOT NULL,
  week_part   integer NOT NULL,
  month_part  integer NOT NULL,
  qtr_part    integer NOT NULL, 
  year_part   integer NOT NULL, 
);

which separates dates in report ranges.

I need to write queries that will allow me to do the following:

• Return the change in a week a week sales_amt during the given period. For example, the change between sales today and sales N days ago - where N is a positive integer (N == 7 in this case).

• sales_amt . (1). . , ( ) , .

, , , SQL - . , SQL , DB (, ANSI SQL).

Answer 1

, , , - , .

, 2010

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

CalendarYearWeek dimDate, varchar (8), '2010-w03', Year dimDate .

, , , .

dimDate :

WeekNumberInEpoch, - , - . dimDate WeekNumberInEpoch.

DayOfWeek, varchar (10) - 'sunday', 'monday',...

DayNumberInWeek, integer - 1-7

CTE, PostgreSQL, SQL Server, Oracle, DB2. CTE (q_00) .

-- 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 ;

Answer 2

"" ( , (, , , ), .

(1.) (2.) .

, SQL /.. (-er) , ....