2023年2月 – 东方无解

2023年2月24日2023年2月24日

PG建立分区

# 建父表
create table sales_detail (
	product_id int not null,
	price numeric(12,2),
	amount int not null,
	sale_date date not null,
	buyer varchar(40),
	buyer_contarct text
);
# 建子表
create table sales_detail_y2014m01(check (sale_date >= date '2014-01-01' and sale_date < date '2014-02-01')) inherits (sales_detail);
create table sales_detail_y2014m02(check (sale_date >= date '2014-02-01' and sale_date < date '2014-03-01')) inherits (sales_detail);
create table sales_detail_y2014m03(check (sale_date >= date '2014-03-01' and sale_date < date '2014-04-01')) inherits (sales_detail);
create table sales_detail_y2014m04(check (sale_date >= date '2014-04-01' and sale_date < date '2014-05-01')) inherits (sales_detail);
create table sales_detail_y2014m05(check (sale_date >= date '2014-05-01' and sale_date < date '2014-06-01')) inherits (sales_detail);
create table sales_detail_y2014m06(check (sale_date >= date '2014-06-01' and sale_date < date '2014-07-01')) inherits (sales_detail);
create table sales_detail_y2014m07(check (sale_date >= date '2014-07-01' and sale_date < date '2014-08-01')) inherits (sales_detail);
create table sales_detail_y2014m08(check (sale_date >= date '2014-08-01' and sale_date < date '2014-09-01')) inherits (sales_detail);
create table sales_detail_y2014m09(check (sale_date >= date '2014-09-01' and sale_date < date '2014-10-01')) inherits (sales_detail);
create table sales_detail_y2014m10(check (sale_date >= date '2014-10-01' and sale_date < date '2014-11-01')) inherits (sales_detail);
create table sales_detail_y2014m11(check (sale_date >= date '2014-11-01' and sale_date < date '2014-12-01')) inherits (sales_detail);
create table sales_detail_y2014m12(check (sale_date >= date '2014-12-01' and sale_date < date '2015-01-01')) inherits (sales_detail);
# 字表索引
create index sale_detail_y2014m04_sale_date on sales_detail_y2014m01 (sale_date);
create index sale_detail_y2014m02_sale_date on sales_detail_y2014m02 (sale_date);
create index sale_detail_y2014m03_sale_date on sales_detail_y2014m03 (sale_date);
create index sale_detail_y2014m04_sale_date on sales_detail_y2014m04 (sale_date);
create index sale_detail_y2014m05_sale_date on sales_detail_y2014m05 (sale_date);
create index sale_detail_y2014m06_sale_date on sales_detail_y2014m06 (sale_date);
create index sale_detail_y2014m07_sale_date on sales_detail_y2014m07 (sale_date);
create index sale_detail_y2014m08_sale_date on sales_detail_y2014m08 (sale_date);
create index sale_detail_y2014m09_sale_date on sales_detail_y2014m09 (sale_date);
create index sale_detail_y2014m10_sale_date on sales_detail_y2014m10 (sale_date);
create index sale_detail_y2014m12_sale_date on sales_detail_y2014m12 (sale_date);
# 建触发器
CREATE OR REPLACE FUNCTION sale_detail_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.sale_date >= DATE '2014-01-01' AND
NEW. sale_date < DATE '2014-02-01' ) THEN
INSERT INTO sales_detail_y2014m01 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-02-01' AND
NEW.sale_date < DATE '2014-03-01' ) THEN
INSERT INTO sales_detail_y2014m02 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-03-01' AND
NEW.sale_date < DATE '2014-04-01' ) THEN
INSERT INTO sales_detail_y2014m03 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-04-01' AND
NEW.sale_date < DATE '2014-05-01' ) THEN
INSERT INTO sales_detail_y2014m04 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-05-01' AND
NEW.sale_date < DATE '2014-06-01' ) THEN
INSERT INTO sales_detail_y2014m05 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-06-01' AND
NEW.sale_date < DATE '2014-07-01' ) THEN
INSERT INTO sales_detail_y2014m06 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-07-01' AND
NEW.sale_date < DATE '2014-08-01' ) THEN
INSERT INTO sales_detail_y2014m07 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-08-01' AND
NEW.sale_date < DATE '2014-09-01' ) THEN
INSERT INTO sales_detail_y2014m08 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-09-01' AND
NEW.sale_date < DATE '2014-10-01' ) THEN
INSERT INTO sales_detail_y2014m09 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-10-01' AND
NEW.sale_date < DATE '2014-11-01' ) THEN
INSERT INTO sales_detail_y2014m10 VALUES (NEW.*);

ELSIF (NEW.sale_date >= DATE '2014-11-01' AND
NEW.sale_date < DATE '2014-12-01' ) THEN
INSERT INTO sales_detail_y2014m11 VALUES (NEW.*);

ELSIF ( NEW.sale_date >= DATE '2014-12-01' AND
NEW.sale_date < DATE '2015-01-01' ) THEN
INSERT INTO sales_detail_y2014m12 VALUES (NEW.*);

ELSE
RAISE EXCEPTION 'Date out of range. Fix the
sale_detail_insert_trigger () function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_sale_detail_trigger
BEFORE INSERT ON sales_detail
FOR EACH ROW EXECUTE PROCEDURE
sale_detail_insert_trigger ();

# 数据插入：
insert into sales_detail values(1,43.12,1,date '2014-01-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-02-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-03-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-04-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-05-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-06-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-07-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-08-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-09-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-10-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-11-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-12-02', '李四', '四川yy');
insert into sales_detail values(1,43.12,1,date '2014-01-02', '李四', '四川yy');

# 数据测试
select * from sales_detail limit 10;

select * from sales_detail_y2014m01 limit 1;

规则实现触发器功能

CREATE RULE sales_detail_insert_y2014m01 AS

ON INSERT TO sales_detail WHERE

( sale_date >= DATE '2014-01-01' AND sale_date < DATE
'2014-02-01' )
DO INSTEAD
INSERT INTO sales_detail_y2014m01 VALUES (NEW.);
CREATE RULE sales_detail_insert_y2014m02 AS
ON INSERT TO sales_detail WHERE
( sale_date >= DATE '2014-02-01' AND sale_date < DATE
'2014-03-01' )
DO INSTEAD
INSERT INTO sales_detail_y2014m01 VALUES (NEW.);
….
CREATE RULE sales_detail_insert_y2014m12 AS
ON INSERT TO sales_detail WHERE
( sale_date >= DATE '2014-12-01' AND sale_date < DATE

'2015-01-01' )

DO INSTEAD

INSERT INTO sales_detail_y2014m12 VALUES (NEW.*);

2023年2月20日2023年2月20日

case when

建表插入数据

create table score(
 stu_code varchar(8) not null,
 stu_name varchar(8) not null,
 stu_sex int not null,
 stu_score int not null)
 ;
insert into score values('xm','小明',0,88);
insert into score values('xl','小磊',0,55);
insert into score values('xf','小峰',0,45);
insert into score values('xh','小红',0,66);
insert into score values('xn','晓妮',0,77);
insert into score values('xy','小伊',0,99);

SELECT 
SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,  --男生数量统计
SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,  --女生数量统计
SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,  --男生通过人数统计
SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS  --女生数量统计
FROM score

postgres=# select (case when stu_sex =0 then 1 else 0 end) from thtf_students;

case

(6 rows)

postgres=# select case when stu_sex =0 then 1 else 0 end from thtf_students;

case

(6 rows)

2023年2月14日2023年2月14日

select 学习

关联表

CREATE TABLE class(no int primary key, class_name varchar(40));
INSERT INTO class VALUES(1,'初二(1)班');
INSERT INTO class VALUES(2,'初二(2)班');
INSERT INTO class VALUES(3,'初二(3)班');
INSERT INTO class VALUES(4,'初二(4)班');
CREATE TABLE student(no int primary key, student_name varchar(40), age int, class_no int);
INSERT INTO student VALUES(1, '张三', 14, 1);
INSERT INTO student VALUES(2, '吴二', 15, 1);
INSERT INTO student VALUES(3, '李四', 13, 2);
INSERT INTO student VALUES(4, '吴三', 15, 2);
INSERT INTO student VALUES(5, '王二', 15, 3);
INSERT INTO student VALUES(6, '李三', 14, 3);
INSERT INTO student VALUES(7, '吴二', 15, 4);
INSERT INTO student VALUES(8, '张四', 14, 4);

1、标准
SELECT student_name, class_name FROM student,
class
WHERE student.class_no = class.no;

2、distinct
select distinct student_name, class_no from student, class;

3、
select student_name, class_name from student, class;

oldboy=# select * from student a, class b where a.class_no=b.no and b.class_name='初二(2)班';
 no | student_name | age | class_no | no | class_name 
----+--------------+-----+----------+----+------------
  3 | 李四         |  13 |        2 |  2 | 初二(2)班
  4 | 吴三         |  15 |        2 |  2 | 初二(2)班
(2 rows)

oldboy=# explain analyze select * from student a, class b where a.class_no=b.no and b.class_name='初二(2)班';
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=17.79..35.25 rows=3 width=212) (actual time=0.020..0.022 rows=2 loops=1)
   Hash Cond: (a.class_no = b.no)
   ->  Seq Scan on student a  (cost=0.00..15.90 rows=590 width=110) (actual time=0.007..0.008 rows=8 loops=1)
   ->  Hash  (cost=17.75..17.75 rows=3 width=102) (actual time=0.008..0.008 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on class b  (cost=0.00..17.75 rows=3 width=102) (actual time=0.005..0.006 rows=1 loops=1)
               Filter: ((class_name)::text = '初二(2)班'::text)
               Rows Removed by Filter: 3
 Planning Time: 0.080 ms
 Execution Time: 0.038 ms
(10 rows)

oldboy=# explain analyze select * from student where class_no in  (select no from class where class_name='初二(2)班');
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=17.79..35.25 rows=3 width=110) (actual time=0.019..0.021 rows=2 loops=1)
   Hash Cond: (student.class_no = class.no)
   ->  Seq Scan on student  (cost=0.00..15.90 rows=590 width=110) (actual time=0.006..0.007 rows=8 loops=1)
   ->  Hash  (cost=17.75..17.75 rows=3 width=4) (actual time=0.007..0.008 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on class  (cost=0.00..17.75 rows=3 width=4) (actual time=0.005..0.005 rows=1 loops=1)
               Filter: ((class_name)::text = '初二(2)班'::text)
               Rows Removed by Filter: 3
 Planning Time: 0.089 ms
 Execution Time: 0.038 ms
(10 rows)