All Forums Analytics
Sue73 6 posts Joined 02/14
18 Sep 2014
Teradata OLAP functions in SQL

Hello all,
I need your advise on writing the following query in a more efficient way using OLAP functions like sum() over partition by. Instead of using 2 queries, can I use just one query to accomplish the same results?
btw, query p returns 2 rows and query q has 10 rows but the final result should render only 2 rows (with the common prd_id from the 1st query).
Please advise. 
Thanks much,
Sue.
select x.prd_id,
 x.prd_name,
 x.p_amt,
 x.p_cnt,
 x.px_amt,
 x.px_cnt,
 x.ap_amt,
 x.ap_cnt,
 x.apx_amt,
 x.apx_cnt
from
(
(
select  a.prd_id,
  a.prd_name,
 sum(case when (a.prd_cd='X' and a.prd_name like b.prdname) then a.amt else 0 end) as  p_amt,
 sum(case when (a.prd_cd='X' and a.prd_name like b.prdname) then 1 else 0 end) as  p_cnt,
 sum(case when (a.prd_cd='Y' and a.prd_name like b.prdname) then a.amt else 0 end) as  px_amt,
 sum(case when (a.prd_cd='Y' and a.prd_name like b.prdname) then 1 else 0 end) as  px_cnt,
from t_prod a,
 (select prdname from prdlkup) b
group by 1,2
) p
inner join
(
select  a.prd_id,
  a.prd_name,
 sum(case when a.prd_cd='X'  then a.amt else 0 end) as  ap_amt,
  sum(case when a.prd_cd='X'  then 1 else 0 end) as  ap_cnt,
  sum(case when a.prd_cd='Y' then a.amt else 0 end) as  apx_amt,
  sum(case when a.prd_cd='Y'  then 1 else 0 end) as  apx_cnt,
from t_prod a
group by 1,2
on
) q
where p.prd_id = q.prd_id
) x
 

Sue
dnoeth 4628 posts Joined 11/04
19 Sep 2014

Hi Sue,
the p query is strange, do you really need a cross join between a and b?
Is b.prdname actually including wildcards?
Can you show some input/expected result set?

Dieter

Sue73 6 posts Joined 02/14
19 Sep 2014

Hi dnoeth,
Thanks so much for your response!! Yes, b.prdname includes wildcards like '%abc%', '%pqr%', '%aabc%', '%xyz%'. This lookup table is a single column table that has about 90 string patterns. That is the reason why I had to use a cross join.
The first query p should render aggregates based on prd_cd and matching string patterns(there are multiple prd_names for prd_id). The result for this query gives 2 records with aggregates where the prd_name matches the string patterns.
The second query q renders say 10 records with all the aggregates for all the prd_names by prd_id irrespective of the string patterns. The final query should have 2 records from the p query and the matching prd_id with its aggregates from q query. 
This query works fine but I would like know if we have a much better way of writing sql in teradata using olap functions like sum() over partition by  or count() over partition by etc., meaning instead of writing 2 subqueries can I accomplish the same with one query.
Please advise.
As always thanks so much for all your help!
Sue

Sue

Sue73 6 posts Joined 02/14
22 Sep 2014

Hi Dieter,
Can you please advise if there is a better way to write the query above without using two derived tables?
Thanks for your help!!
Sue

Sue

dnoeth 4628 posts Joined 11/04
23 Sep 2014

Hi Sue,
I'm not sure, but you might do the 2nd sum in a Derived Table and then cross join to prdlkup and use a SUM OVER like this, fully untested :-)

select  a.prd_id,
  a.prd_name,
  ap_amt,
  ap_cnt,
  apx_amt,
  apx_cnt,
  sum(case when (a.prd_cd='X' and a.prd_name like b.prdname) then a.amt else 0 end) over () as  p_amt,
  sum(case when (a.prd_cd='X' and a.prd_name like b.prdname) then 1 else 0 end) over () as  p_cnt,
  sum(case when (a.prd_cd='Y' and a.prd_name like b.prdname) then a.amt else 0 end) over () as  px_amt,
  sum(case when (a.prd_cd='Y' and a.prd_name like b.prdname) then 1 else 0 end) over () as  px_cnt,
from
 (
   select  a.prd_id,
     a.prd_name,
    sum(case when a.prd_cd='X'  then a.amt else 0 end) as  ap_amt,
     sum(case when a.prd_cd='X'  then 1 else 0 end) as  ap_cnt,
     sum(case when a.prd_cd='Y' then a.amt else 0 end) as  apx_amt,
     sum(case when a.prd_cd='Y'  then 1 else 0 end) as  apx_cnt,
   from t_prod a
   group by 1,2
 ) as a,
 (select prdname from prdlkup) b

 

Dieter

Sue73 6 posts Joined 02/14
23 Sep 2014

Hi Dieter,
Thanks so much for your help and time!! You're the best! :)
Thanks
Sue

Sue

You must sign in to leave a comment.