数据库与数据分析教学

CREATE TABLE centres (
  centre_id TEXT PRIMARY KEY,
  centre_name TEXT NOT NULL,
  capacity INTEGER CHECK (capacity >= 0)
);
CREATE TABLE enrolments_raw (
  source_row INTEGER PRIMARY KEY,
  record_id TEXT NOT NULL,
  centre_id TEXT NOT NULL,
  week TEXT NOT NULL,
  enrolled INTEGER NOT NULL,
  attended INTEGER
);
INSERT INTO centres VALUES
 ('C01','Demo Centre A',24),('C02','Demo Centre B',20),
 ('C03','Demo Centre C',18),('C04','Demo Centre D',NULL);
INSERT INTO enrolments_raw VALUES
 (1,'E01','C01','W1',20,18),(2,'E02','C02','W1',15,NULL),
 (3,'E03','C03','W1',12,0),(4,'E04','C99','W1',8,6),
 (5,'E05','C01','W2',21,19),(6,'E05','C01','W2',21,19),
 (7,'E06','C02','W2',16,14),(8,'E07','C04','W1',9,8);
-- Keep staging records intact. Inspect repeats before choosing a policy.
CREATE VIEW enrolments_clean AS
 SELECT source_row,record_id,centre_id,week,enrolled,attended
 FROM (SELECT *, ROW_NUMBER() OVER
       (PARTITION BY record_id ORDER BY source_row) AS occurrence
       FROM enrolments_raw) ranked
 WHERE occurrence = 1;