刀塔传奇公会管理系统 ------ Pgsql 后台

背景：

系统会给出公会人员的情况，例如 几月几日XXXX加入公会，几月几日XXXX离开工会。

系统会给出所有人最近七天的活跃总数。

 

需要计算出所有人最近七天的平均活跃情况，加入时间不足七天的玩家，按实际天数计算。

 

数据存储方式选择：

1.   开始使用的Excel做的，编写了一些宏，增加了一个按钮来触发这个宏。数据稍微一多，弊病就露出来了。

     输入活跃数据时，需要手动寻找名字在第几行。多天的数据整理需要手工处理，数据量大时很容易造成数据错乱或数据丢失。

2.  计划使用Access数据库来存储，但发现Access的查询分析器需要单独下载，并且每次仅支持一句SQL执行。

3.  更换Window下的MySql来存储数据。一切安装完毕后，创建数据库的表后，发现表的内容不能很好的支持中文。编码问题，网上简单查找了一下解决办法，貌似需要新版的，而且仍然需要操作很多。有需要的可以参考一下http://www.jb51.net/article/18560.htm

4.  本人比较数据Pgsql，还是用这个吧，不存在编码问题，完全免费开源。经过一番折腾，终于在Win7下搞定了。

 

数据库设计：

为了达到好用的效果，又去翻了书本温习了范式的概念，感觉关系数据库能做到3NF就不错了。

为了不产生大量的冗余数据，计划讲成员管理与贡献值数据分离。Member表中主键打算用游戏内的中文名字（也可以给每个成员定一个内部的id，效果相差应该不多）。

member表中的字段为 name，indate，outdate。name和indate字段为Not Null。若outdate为Null表示成员当前还在工会。

data表中的数据每天会新增约50条数据，当时间久了会产生一张很大的表，势必会影响访问效率。到时候想再变动就很困难了。为此计划data表按照日期来命名，也就是说每天都会产生一个新表出来。表内的字段只有一个，就是整数类型的活跃值。

 

源码：

-- Table: gonghui.member

-- DROP TABLE gonghui.member;

CREATE TABLE gonghui.member
(
  name character varying(20) NOT NULL,
  indate character varying(20) NOT NULL,
  outdate character varying(20),
  CONSTRAINT member_pkey PRIMARY KEY (name )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE gonghui.member
  OWNER TO root;

-- Table: gonghui.data20141209

-- DROP TABLE gonghui.data20141209;

CREATE TABLE gonghui.data20141209
(
  name character varying(20) NOT NULL,
  data integer,
  CONSTRAINT data20141209_pkey PRIMARY KEY (name ),
  CONSTRAINT data20141209_name_fkey FOREIGN KEY (name)
      REFERENCES gonghui.member (name) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE gonghui.data20141209
  OWNER TO root;

-- Function: gonghui.lesssevendays(integer)

-- DROP FUNCTION gonghui.lesssevendays(integer);

CREATE OR REPLACE FUNCTION gonghui.lesssevendays(days integer)
  RETURNS integer AS
$BODY$
BEGIN

    if (days > 7 ) then
        return 7;
    end if;

    if (days < 1 ) then
        return 1;
    end if;
    return days;
END;


$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION gonghui.lesssevendays(integer)
  OWNER TO root;

-- Function: gonghui.fn_calc_result(character varying, character varying)

-- DROP FUNCTION gonghui.fn_calc_result(character varying, character varying);

CREATE OR REPLACE FUNCTION gonghui.fn_calc_result(psv_date character varying, psv_compare_date character varying)
  RETURNS integer AS
$BODY$

declare

PSV_SQL character varying(512) := '';
PSV_TBL_NEW character varying(512) := '';
PSV_TBL_OLD character varying(512) := '';


BEGIN

drop table dataresult;

PSV_TBL_NEW := 'data' || psv_date;
PSV_TBL_OLD := 'data' || psv_compare_date;


PSV_SQL := '
create table dataresult as select
 m.Name as name, 
 lesssevendays(to_date(''' || psv_date || ''','''|| 'yyyymmdd'''|| ') - date(m.Indate)) ,
 d.data/ lesssevendays(to_date(''' || psv_date  || ''',''' || 'yyyymmdd'''|| ') - date(m.Indate)) as avg_data,
 d.data as newdata,
 y.data as olddata
 from member m,  ' || PSV_TBL_NEW || ' d,  '|| PSV_TBL_OLD ||' y
 where m.Name = d.name and m.Outdate = '''' and m.name = y.name';


    EXECUTE(PSV_SQL);
    return 0;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION gonghui.fn_calc_result(character varying, character varying)
  OWNER TO root;