Need help on Roll- up Query to aggregated in Teradata

Database

Need help on Roll- up Query to aggregated in Teradata

Hi Geeks,

User wanted to roll-up this data(see below) by weekly, daily, monthly & Yearly as a aggregated query in Teradata, could you please help with a Roll- up Query.

In addition, Need the max(sale_id) for the same store_id

Here is the Query in SQL:

CREATE TABLE dbo.SALES
(
SALE_ID int NOT NULL,

SALE_Amount int NOT NULL
CREATE_DATE
ITEM_ID smallint NOT NULL,
STORE_ID PRIMARY KEY,
ITEM_TYPE smallint NOT NULL
CUSTOMER_ID varchar(25) NULL,
INVENTORY_ID varchar(25) NULL
)

 

Sample Data:

SALE IDCREATE_DATEITEM_IDSTORE_IDITEM_TYPECUSTOMER_IDINVENTORY_IDSALE AmountMonthYearWeekMonth
102019-01-01 22:22:205678904109Google IVR262752606175c1308de3ff20120191January
202019-01-02 22:22:205678914109Amazon Alexa26275260618905-df8e9b64c27c39.2120191January
342019-01-04 22:22:205678904109Samsung TV 46 LED3368704416 78.9120191January
542019-01-05 22:22:205678924109Samsung TV 46 LED9196331924 152.74120191January
642018-10-02 02:42:555678924109Samsung TV 60 LED801455353594d0-2bfeff573c91166.1110201840October
852018-10-01 23:16:055678924109Samsung TV 46 LCD443880712394d0-2bfeff573c92192.8510201840October
952018-10-01 23:02:495678924109Samsung SpeakerUNKNOWN8f04-84736ef7cc1123.810201840October
1052018-10-02 00:55:205678934109AlexaUNKNOWN94d0-2bfeff573c9378.910201840October
1152018-10-01 18:53:465678934109Samsung TV 46 LED20554128808f04-84736ef7cc126710201840October
1252018-10-01 21:28:355678934109LG TV 46 LEDUNKNOWN94d0-2bfeff573c9485.8910201840October
1362018-10-01 13:32:565678934109LG TV 42 LED20140785748f04-84736ef7cc1399.2610201840October
1462018-10-02 01:00:075678934109LG TV 50 LED909210908694d0-2bfeff573c95112.6310201840October
1562018-10-01 23:16:435678944109LG TV 36 LED58621440238f04-84736ef7cc1412610201840October
1662018-10-01 16:46:085678944109Samsung TV 42 LEDUNKNOWN94d0-2bfeff573c96139.3710201840October
1762018-10-01 16:00:375678944109Samsung TV 46 LED81680011218f04-84736ef7cc15152.7410201840October
1872018-10-01 22:18:065678944109Samsung TV 46 LED701430173394d0-2bfeff573c97166.1110201840October
1972018-09-02 01:12:585678944109Samsung TV 60 LED91620146018f04-84736ef7cc16179.489201836September
2072018-09-03 01:12:585678954109Samsung TV 75 LED605237249794d0-2bfeff573c98192.859201836September
2172018-09-04 01:12:585678954109Samsung TV 46 LCD63168174688f04-84736ef7cc17206.229201836September
2272018-09-05 01:12:585678954109Samsung Speaker508277455694d0-2bfeff573c99 9201836September
5 REPLIES 5
Teradata Employee

Re: Need help on Roll- up Query to aggregated in Teradata

Hi Rosana_NewBee,

 

You forgot half the question here.

Rollup what metrics against what axes ?

 

Re: Need help on Roll- up Query to aggregated in Teradata

Group by sale_id& store_id, In addition need max(Date) on Item_ID level of granularity.

Teradata Employee

Re: Need help on Roll- up Query to aggregated in Teradata

Ok that's good - to be precise what do you expect as output considering your sample datas ?

Re: Need help on Roll- up Query to aggregated in Teradata

Aggregated to monthly /weekly & grouping by Store_Id:

For Ex: Store _ID 4109 monthly breakdown for Jan: 314.64

            Store _ID 5109, monthly breakdown for Jan: 23.8

            Store _ID 5109 weekly breakdown for Jan'1 week: 23.8

 

Highlighted
Teradata Employee

Re: Need help on Roll- up Query to aggregated in Teradata

Hi Rosana,

 

Is it something like this you're looking for ?

Data

create multiset volatile table mvt_sales, no log
( sale_id         smallint     not null
, create_date     timestamp(0) not null
, store_id        smallint     not null
, sale_amount     decimal(5,2) not null
)
primary index (store_id)
on commit preserve rows;

insert into mvt_sales values ( 10, cast('2019-01-01 22:22:20' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109,  20   );
insert into mvt_sales values ( 20, cast('2019-01-02 22:22:20' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109,  39.2 );
insert into mvt_sales values ( 34, cast('2019-01-04 22:22:20' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109,  78.9 );
insert into mvt_sales values ( 54, cast('2019-01-05 22:22:20' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109, 152.74);
insert into mvt_sales values ( 64, cast('2018-10-02 02:42:55' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109, 166.11);
insert into mvt_sales values ( 85, cast('2018-10-01 23:16:05' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109, 192.85);
insert into mvt_sales values ( 95, cast('2018-10-01 23:02:49' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109,  23.8 );
insert into mvt_sales values (105, cast('2018-10-02 00:55:20' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109,  78.9 );
insert into mvt_sales values (115, cast('2018-10-01 18:53:46' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109,  67   );
insert into mvt_sales values (125, cast('2018-10-01 21:28:35' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109,  85.89);
insert into mvt_sales values (136, cast('2018-10-01 13:32:56' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109,  99.26);
insert into mvt_sales values (146, cast('2018-10-02 01:00:07' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109, 112.63);
insert into mvt_sales values (156, cast('2018-10-01 23:16:43' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109, 126   );
insert into mvt_sales values (166, cast('2018-10-01 16:46:08' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109, 139.37);
insert into mvt_sales values (176, cast('2018-10-01 16:00:37' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109, 152.74);
insert into mvt_sales values (187, cast('2018-10-01 22:18:06' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109, 166.11);
insert into mvt_sales values (197, cast('2018-09-02 01:12:58' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109, 179.48);
insert into mvt_sales values (207, cast('2018-09-03 01:12:58' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109, 192.85);
insert into mvt_sales values (217, cast('2018-09-04 01:12:58' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109, 206.22);
insert into mvt_sales values (227, cast('2018-09-05 01:12:58' as timestamp(0) format 'yyyy-mm-ddbhh:mi:ss'), 4109,   0   );

Queries

-- Year / Month
select store_id , to_char(create_date, 'yyyy') as sale_year , to_char(create_date, 'yyyy-mm') as sale_month , sum(sale_amount) from mvt_sales where store_id = 4109 group by store_id , rollup(to_char(create_date, 'yyyy'), to_char(create_date, 'yyyy-mm'));
-- IsoYear / IsoWeek select store_id , to_char(create_date, 'iyyy') as sale_isoyear , to_char(create_date, 'iyyy"W"iw') as sale_isoweek , sum(sale_amount) from mvt_sales where store_id = 4109 group by store_id , rollup(to_char(create_date, 'iyyy'), to_char(create_date, 'iyyy"W"iw'));