BI 中关于度量的SQL计算

with 语句是执行一次并并存储在temp 表空间中。

1.逻辑层：计算度量值:

SET VARIABLE QUERY_SRC_CD='Report';
SELECT Times."Calendar Year" saw_0,
   "Sales Facts"."Amount Sold" saw_1,
   "Sales Facts"."Unit Cost" saw_2,
   "Sales Facts"."Gross Profit" saw_3
FROM SH ORDER BY saw_0 ;

WITH
SAWITH0 AS (select sum(T168.UNIT_COST) as c1,
     sum(T245.AMOUNT_SOLD) as c2,
     T268.CALENDAR_YEAR as c3
from
     SH.TIMES T268,
     SH.COSTS T168,
     SH.SALES T245
where (
T168.PROD_ID = T245.PROD_ID and
T168.TIME_ID = T245.TIME_ID and
T168.PROMO_ID = T245.PROMO_ID and
T168.CHANNEL_ID = T245.CHANNEL_ID and
T245.TIME_ID = T268.TIME_ID )
group by T268.CALENDAR_YEAR)
select distinct SAWITH0.c3 as c1,
     SAWITH0.c2 as c2,
     SAWITH0.c1 as c3,
     SAWITH0.c2 - SAWITH0.c1 as c4
from
     SAWITH0
order by c1

2.物理层：计算度量值: 为什么不用with,没有用到子查询
SET VARIABLE QUERY_SRC_CD='Report';
SELECT Times."Calendar Year" saw_0,
"Sales Facts"."Amount Sold" saw_1,
"Sales Facts"."Unit Cost" saw_2,
"Sales Facts"."Gross Profit Physical" saw_3
FROM SH ORDER BY saw_0 ;

select T268.CALENDAR_YEAR as c1,
     sum(T245.AMOUNT_SOLD) as c2,
     sum(T168.UNIT_COST) as c3,
     sum(T245.AMOUNT_SOLD - T168.UNIT_COST) as c4
from
     SH.TIMES T268,
     SH.COSTS T168,
     SH.SALES T245
where (
T168.PROD_ID = T245.PROD_ID and
T168.TIME_ID = T245.TIME_ID and
T168.PROMO_ID = T245.PROMO_ID and
T168.CHANNEL_ID = T245.CHANNEL_ID and
T245.TIME_ID = T268.TIME_ID )
group by T268.CALENDAR_YEAR
order by c1

3.使用anayltics answer进行度量值的计算:

SET VARIABLE QUERY_SRC_CD='Report';

SELECT Times."Calendar Year" saw_0,
"Sales Facts"."Amount Sold" saw_1,
"Sales Facts"."Unit Cost" saw_2,
"Sales Facts"."Amount Sold"-"Sales Facts"."Unit Cost" saw_3
FROM SH ORDER BY saw_0 ;

WITH
SAWITH0 AS (select sum(T168.UNIT_COST) as c1,
     sum(T245.AMOUNT_SOLD) as c2,
     T268.CALENDAR_YEAR as c3
from
     SH.TIMES T268,
     SH.COSTS T168,
     SH.SALES T245
where ( T168.PROD_ID = T245.PROD_ID
     and T168.TIME_ID = T245.TIME_ID
     and T168.PROMO_ID = T245.PROMO_ID
     and T168.CHANNEL_ID = T245.CHANNEL_ID
     and T245.TIME_ID = T268.TIME_ID )
group by T268.CALENDAR_YEAR)
select distinct SAWITH0.c3
    as c1,
     SAWITH0.c2 as c2,
     SAWITH0.c1 as c3,
     SAWITH0.c2 - SAWITH0.c1 as c4
from
     SAWITH0
order by c1

4.根据计算向导来计算的值
WITH
SAWITH0 AS (select sum(T245.AMOUNT_SOLD) as c1,
     T210.PROD_NAME as c2,
     T210.PROD_ID as c3,
     T210.PROD_CATEGORY as c5
from
     SH.TIMES T268,
     SH.PRODUCTS T210,
     SH.SALES T245
where ( T210.PROD_ID = T245.PROD_ID and T245.TIME_ID = T268.TIME_ID and T268.CALENDAR_YEAR = 2001 )
group by T210.PROD_ID, T210.PROD_NAME, T210.PROD_CATEGORY),
SAWITH1 AS (select sum(SAWITH0.c1) over (partition by SAWITH0.c3, SAWITH0.c5) as c1,
     SAWITH0.c2 as c2,
     SAWITH0.c3 as c3,
     sum(SAWITH0.c1) over (partition by SAWITH0.c5) as c4,
     SAWITH0.c5 as c5
from
     SAWITH0),
SAWITH2 AS (select distinct SAWITH1.c5 as c1,
     SAWITH1.c2 as c2,
     SAWITH1.c1 as c3,
     case when SAWITH1.c4 = 0 then NULL else SAWITH1.c1 * 100.0 / nullif( SAWITH1.c4, 0) end as c4,
     SAWITH1.c3 as c5
from
     SAWITH1)
select SAWITH2.c1 as c1,
     SAWITH2.c2 as c2,
     SAWITH2.c3 as c3,
     SAWITH2.c4 as c4
from
     SAWITH2
order by c1, c2

==================================================================

==================================================================
1.Percent
Case
    when "SH"."Sales Facts"."Category Sales" = 0
   then NULL
    else 100.0 * ("SH"."Sales Facts"."Amount Sold"
/ "SH"."Sales Facts"."Category Sales")
end

2.Index
Case
    when "SH"."Sales Facts"."Category Sales" = 0
      then NULL
    else 1.0 * "SH"."Sales Facts"."Amount Sold" / "SH"."Sales Facts"."Category Sales"
end

3.PresentChange
Case
when ("SH"."Sales Facts"."Category Sales" is NULL or
        "SH"."Sales Facts"."Category Sales" = 0) and
       "SH"."Sales Facts"."Amount Sold" is not NULL and
       "SH"."Sales Facts"."Amount Sold" <> 0
         then 100.0
when ("SH"."Sales Facts"."Category Sales" is NULL or
        "SH"."Sales Facts"."Category Sales" = 0) and
       ("SH"."Sales Facts"."Amount Sold" is NULL or
        "SH"."Sales Facts"."Amount Sold" = 0)
         then 0.0
when "SH"."Sales Facts"."Amount Sold" IS NULL and
       "SH"."Sales Facts"."Category Sales" is not NULL and      "SH"."Sales Facts"."Category Sales" <> 0
         then -100.0
else 100.0 * ("SH"."Sales Facts"."Amount Sold" - "SH"."Sales Facts"."Category Sales") / "SH"."Sales Facts"."Category Sales"
end

4.Change
IfNull("SH"."Sales Facts"."Amount Sold", 0) -
IfNull("SH"."Sales Facts"."Category Sales", 0)