飞雪迎春

        今天使用了递归输出一个棵树，sql如下。WITH RECURSIVE temp_table (DIM_VALUE_ID,DIM_VALUE_NAME,depth) AS(SELECT  root.DIM_VALUE_ID,root.DIM_VALUE_NAME, 0 as depth FROM TAP_C_GRP_DIM_VALUE rootWHERE root.PAR_DIM...

3，累计函数SELECT storeid ,prodid ,sales ,SUM(sales) OVER (ORDER BY sales DESC ROWS UNBOUNDED PRECEDING) FROM salestbl ; 结果如下：storeid prodid sales Cumulativ...

5，剩余视窗聚合（就是除去选中的）SELECT salesdate ,itemid ,sales ,SUM(sales) OVER (ORDER BY salesdate ASC ROWS BETWEEN CURRENT ROW AND unbounded following) AS "AMsum" ,CAST((AMsum -...

例如：SELECT department_number ,SUM(salary_amount)FROM employeeWHERE department_number < 402GROUP BY ROLLUP (department_number)ORDER BY 1; 结果如下：department_number Sum(salary_amount)...

1.常规 写道CREATE TABLE SampDb.PhoneList ,FALLBACK ,NO BEFORE JOURNAL ,NO AFTER JOURNAL ( Number CHAR(10) TITLE 'Number' NOT NULL ,Name CHAR(25) TITLE 'Name' NOT NULL ,LType CHAR(4) TITLE 'Line Type...

1. CREATE VIEW dept(DeptNo, DeptName, Loc, MgrNo)ASSELECT DeptNo (Title 'Department Number') ,DeptName (Title 'Department Name') ,Loc (Title 'Department Location') ,MgrNo (Title 'Manag...

1CREATE INDEX DeptIdx (DeptNo) ON employee;  2CREATE INDEX NamePhone (CustName, Phone) ORDER BY HASH (CustName) ON Customer; 3CREATE INDEX ZipIdx (CustName, ZipCode) OR...

今天开始就不学些Teradata了，开始学习Oracle了。这里标记下，将来或许还会使用Teradata，那个时候继续学习。

show table tablename;help table tablename;SELECT UPPER(TRIM(T1.COLUMNNAME)), COALESCE(T1.COLUMNTITLE,'NO TITLE'), CASE WHEN T1.COLUMNTYPE = 'BF' THEN 'BYT...

Teradata DDL OPERATOR一般用于建立和删除表，也可以用于删除Load OPERATOR时创建的Error Table。DDL OPERATOR只能用于没有返回结果集的SQL。不能使用select，show，help语句。DEFINE JOB DDL_TEST( DEFINE OPERATOR ddl_operator TYPE DDL ATTRIBUT...

1。聚合SELECT storeid , prodid , sales ,SUM(sales) OVER (ORDER BY sales DESC)FROM salestbl; 结果如下：storeid prodid sales Group Sum(sales) ----------- -----...

今天碰到一个需求需要做趋势分析，后来在高人指点下使用移动差分函数进行处理。虽然使用这个函数，但是要计算出来还是比较复杂的。-- 创建测试表CREATE MULTISET TABLE SXEDRM2.TEST_STDDEV_POP,NO BEFORE JOURNAL,NO AFTER JOURNAL( DEAL_DATE INTEGER, ITEM_ID SMAL...

Teradata根据字段名查询表：select top 100 databasename,tablename,columnname,columntitle,columntype from dbc.columns where databasename= 'iwi' Teradata模糊查询表名：select top 100 databasename,tablename fro...

今天上传了Teradata的递归sql，也把抽样的sql一起贴出来。DATABASE PCDE2;SELECT REGION_CODE,CITY_CODE,CITY_DESC FROM  TB_CDE_LOC_CITY GROUP BY 1QUALIFY RANK(CITY_CODE) < 11;

PPI1.Partitioned Primary Indexes (PPI) PPI是什么:• Teardata中的用在物理数据库设计中的索引机制.• 数据行在AMP级聚合成分区.• 分区只是在AMP上将数据行排序.PPI可以提供的优势:• 增加可用性以提高某些特定查询的效率(特定的分区限制查询).• 只有查询涉及到的特定分区需要被访问,以避免全表扫描.PPI如何创建和管理:• PPI的创建和...

这里以tomcat-5.5.7为例，tomcat 6配置与这不一样。1，先在tomcat-5.5.7\conf\server.xml的<Host></Host>中增加如下代码：<Context crossContext="true" debug="5" docBase="F:\JAVA\FxycWorkSpace\groupkpi\WebRoot" ...

临时表是一种辅助工具，能够提高SQL操作的性能。特别是针对下列情况的SQL操作：1.不能使用规范化的表；2.要求多条 SQL 语句完成。因为有一个需求就是要更新生产系统上的数据，但是生产系统上的数据又要保证正常，5张表之间都有关联关系，因为不能操作生成系统的数据，我的构思是，当用户登录的时候，系统初始，创建5个可变可变临时表，并从生产环境把数据导入可变临时表，用户操作的只是可变临时表，...

之前用Teradata分页都是在先把记录都读取出来然后再分页，这样会消耗大量的内存空间，今天开始使用SQL来分页。分页SQL如下。SELECT DIM_VALUE_ID,DIM_VALUE_NAME, row_number() OVER(ORDER BY DIM_VALUE_ID) RN from TAP_C_GRP_DIM_VALUE QUALIFY RN >10 AND RN...

  一些可能会用到的SQL关键词：  SUM(sales) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SUM(sales) OVER (PARTITION BY prodid ORDER BY sales DESC)SUM(sales) OVER (PARTITION BY s...

创建一个可变临时表的脚本：CREATE VOLATILE TABLE vt_deptsal, LOG(deptno SMALLINT,avgsal DEC(9,2),maxsal DEC(9,2),minsal DEC(9,2),sumsal DEC(9,2),empcnt SMALLINT)ON COMMIT PRESERVE ROWS; ...

CREATE INDEX (empcnt)ON gt_deptsal; CREATE INDEX NamePhone (CustName, Phone) ORDER BY HASH (CustName) ON Customer; CREATE INDEX ZipIdx (CustName, ZipCode) ORDER BY VALUES (ZipCode)...

CREATE TABLE dept1 AS department WITH NO DATA;  CREATE TABLE dept1, NO FALLBACK AS department WITH NO DATAUNIQUE INDEX (department_name); CREATE TABLE SampDb.PhoneList,FALLBACK,NO BEFO...

用于执行操作系统指令，如：FPT，文件夹创建，和数据文件备份等操作。DEFINE JOB OS_TEST( DEFINE OPERATOR os_operator DESCRIPTION 'OS OPERTATOR' TYPE OS COMMAND ATTRIBUTES ( VARCHAR OsCmd = 'mkdir abc1', VARCHAR Ig...