[置顶] HANA基础篇 YTD实例

   基于之前写的一些语法 而没有实例讲解 可能会让多数人觉得枯燥或吃力。所以我今天准备了一个实例 跟大家一起分享 

   案例要求：

   现有合同表一份,要求计算任意指定范围内的每个月的YTD(全部同时列出)

   (YTD的概念，百度上自己去查）

   输出字段: 年月, YTD金额

  合同表的结构和数据如下:

  create column table "HANABI"."CONTRACTS"

       ("CONTRACT_ID"INTEGER null, --合同ID

        "START_DATE" DATE null, --开始日期

        "END_DATE" DATE null,          --结束日期

        "AMOUNT" DECIMAL (18,2) null)    --每月金额

     

insert into "HANABI"."CONTRACTS"VALUES(1,'2012-04-01', '2013-06-30',100);

insert into "HANABI"."CONTRACTS"VALUES(2,'2013-07-01', '2015-03-31',100);

insert into "HANABI"."CONTRACTS"VALUES(3,'2014-02-01', '2015-07-31',100);

insert into "HANABI"."CONTRACTS"VALUES(4,'2014-05-01', '2016-10-31',100);

insert into "HANABI"."CONTRACTS" VALUES(5,'2014-07-01','2015-05-31',100);

insert into "HANABI"."CONTRACTS"VALUES(6,'2015-01-01', '2016-02-29',100);

insert into "HANABI"."CONTRACTS"VALUES(7,'2015-08-01', '2016-09-30',100);

insert into "HANABI"."CONTRACTS"VALUES(8,'2014-02-01', '2014-12-31',100);

insert into "HANABI"."CONTRACTS"VALUES(9,'2015-02-01', '2015-03-30',100);

 

日期表结构,自行设计.日期表要具有通用性,不允许包含任何合同信息.

 

要求:建立一个带参数计算视图,分别用SQL语句和图形的方式建立.

建立一个存储过程，每个月计算YTD，如何计划执行该存储过程。

问：输入时间参数201406-201506求YTD

 

从题目的要求中 我们可以抓住5个点：

1. YTD概念：从年初到当前月份

2.日期表需要自行设计

3.分别用HANA中图形和SQL语句方式建立计算视图

4.还需要写一个procedure

5.还需要设置一个过滤器来筛选时间

过程：

首先，我们对合同表的时间进行分析 可以联想时间格式设置为YYYYMM 所以我们对时间表的设计即可以设置Year、Month 但是我们可能后续还需要对其进行计算 所以在放入两个int型的数据Year_int and Month_int。

那我们如何对合同表和日期表进行关联呢？

所以我们可以在日期表中多加入一个伪列connact作为join列 在后续的方法中连接。

设计完毕后 做成excel 然后导入到hana数据库中 最终结构如下所示（数据自己添加）

  

合同表按sql语句创建完毕以及插入数据（略过）

接下来进行属性模型的创建（其实到后面熟练 只需要在计算视图中写即可 这里为了进行演示 详细介绍）

为了将日期格式都设置为YYYYMM格式 所以我们可以用一些函数记性设计 （我这里生成的格式是201401 但是到10 11 12这种月份 就变成2014011类型 后面在进行二次转换）

最后属性模型应该如下所示

接下来我们进行建立分析模型

因为其中存在amount 度量 所以将合同表放入其中

因为合同表的日期数据不是YYYYMM所以我们要将其进行格式转换 且我们还要顺道将year和month int话 方便后面计算（只举两个转换方法 其他自行修改）

接下来进入计算模型的创建了 首先我们进行图表方式创建计算模型

因为我在属性视图那里YYYYMM格式 遇到11月这种双数月 就会出现YYYY0MM格式 所以 我在进行一次转化 将其彻底变成YYYYMM

然后进行连接（因为在分析模型中 我们对合同表也插入了一行伪列 connact 所以我们可以用join将其连接起来）

对年份进行一层过滤 筛选出符合YTD的年份

所以在join出我们写了一个FLAG条件 然后在 Projection_3处过滤（只有月份大于等于start并且小于等于end才可以通过）

过滤后 就要进行YTD计算 所以同理 我在本处写了一个YTD计算（YM小于等于end的时候 跳转到 另一个if中 如果year_int=stat_int 则代表同年 直接month_int-start_month_int即可 如果不是则代表不同年 所以用month_int即可）

最后在Semantics中设置维度和度量 以及对YM添加过滤器

最后激活 输入参数 得出结果

这样图形创建计算视图就解决了

接下来就是sql语句创建计算视图了

（sql语句创建  自己好好看看我前面发的sql基础篇 或者自己百度 谷歌多查查相关方面的用法理解 就不详细介绍了）

sql代码如下：

 
 /********* Begin Procedure Script ************/ 
 BEGIN 
  var_out = 
  SELECT YEARMONTHS,SUM(
        CASE WHEN"YEARMONTHS">"END_TIME" AND TO_INT(LEFT(START_TIME,4))=TO_INT(LEFT(END_TIME,4)) THEN (TO_INT(RIGHT(END_TIME,2))-              TO_INT(RIGHT(START_TIME,2))+1)*AMOUNT
WHEN"YEARMONTHS">"END_TIME" AND TO_INT(LEFT(START_TIME,4))<TO_INT(LEFT(END_TIME,4)) THEN TO_INT(RIGHT(END_TIME,2))*AMOUNT
WHEN"YEARMONTHS"<="END_TIME" AND "YEAR_INT"=TO_INT(LEFT(START_TIME,4)) THEN (TO_INT(RIGHT(YEARMONTHS,2)) TO_INT(RIGHT(START_TIME,2))+1)*AMOUNT
ELSE TO_INT(RIGHT(YEARMONTHS,2))*AMOUNT
END) AS YTD
FROM 
  (SELECT YEAR_INT,MONTH_INT,concat(YEAR_INT,MONTHS) AS YEARMONTHS FROM "PRAC_005"."DATES_YTD") DATES,
  (SELECT *,TO_CHAR(START_DATE,'YYYYMM') START_TIME ,TO_CHAR(END_DATE,'YYYYMM') END_TIME FROM "PRAC_005"."CONTRACTS") CONTRACT     
WHERE DATES.YEARMONTHS >= CONTRACT.START_TIME AND TO_INT(LEFT(END_TIME,4)) >= YEAR_INT
GROUP BY YEARMONTHS;

END /********* End Procedure Script ************/


然后设置过滤器 筛选时间 如图表创建计算模型一样 

最后是存储过程的书写 和 sql语句创建计算模型类似

唯一不同的地方就是 存储过程中用sql语句写出了过滤器而已

好了 这个案例就解释清楚了 如果遇到问题的 可以私信我  如果那里有不足的 也可以底下评论指出 

总之 自己也还是博客新手 ~~~~