马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
上一回,我们介绍了数据库视图的基本设置,这里所说的都是,我目前觉得比较好的规范,以后如果有些更好的技能,会再分享 这里先插入说明一下,上一引入的表都是干嘛的,(这个应该在前面说的......) 数据库用的是:PostgreSQL 9.3
咱们这次的例子里,有3张表, 2张维度表: 1. 时间维度
建表语句: [sql] view plaincopy - CREATE TABLE dm_calendar
- (
- calendar_id bigint NOT NULL,
- date_name character varying(200),
- date_name_cn character varying(200),
- current_day numeric(10,0),
- month_id bigint,
- month_name character varying(200),
- month_name_cn character varying(200),
- month_name_short character varying(200),
- month_name_short_cn character varying(200),
- days_in_month numeric(10,0),
- first_of_month numeric(10,0),
- last_month_id numeric(10,0),
- month_end numeric(10,0),
- quarter_id bigint,
- quarter_name character varying(200),
- quarter_name_cn character varying(200),
- quarter_name_short character varying(200),
- quarter_name_short_cn character varying(200),
- year_id bigint,
- year_name character varying(200),
- year_name_cn character varying(200),
- CONSTRAINT dm_calendar_pkey PRIMARY KEY (calendar_id)
- );
这张表里存放了每一天的日期 2. 行政组织维度
建表语句: [sql] view plaincopy - CREATE TABLE dm_dept_org
- (
- dept_dmid bigint,
- levelone_id bigint,
- leverone_name character varying(200),
- leveltwo_id bigint,
- level_two_name character varying(200),
- levelthr_id bigint,
- level_three_name character varying(200),
- level integer,
- name character varying(200),
- org_resp_person_name1 character varying(100),
- org_resp_person_name2 character varying(100),
- org_resp_person_name3 character varying(100)
- );
这张是行政组织维度,平铺存放了每个层级的组织信息 3. 服务监督得分建表语句: [sql] view plaincopy - CREATE TABLE dm_deptserquality_quarterstati
- (
- quarterly_id bigint NOT NULL,
- dept_dmid bigint NOT NULL,
- service_check_score numeric(28,4),
- CONSTRAINT dm_deptserquality_quarterstati_pkey PRIMARY KEY (quarterly_id, dept_dmid)
- );
这张表里,就存了,季度ID,行政组织ID,和一个部门服务得分 4. 关系
|