23 lines
839 B
SQL
23 lines
839 B
SQL
-- vl_ddl_0005.sql - 정합성 DBIO 스키마/뷰 정의 (easy)
|
|
-- 런타임 PostgreSQL 에 적용. 컴파일에는 불필요.
|
|
|
|
CREATE TABLE IF NOT EXISTS vl_ledger (
|
|
key VARCHAR(15) NOT NULL,
|
|
merch_id VARCHAR(15) NOT NULL,
|
|
card_no VARCHAR(19) NOT NULL,
|
|
amount BIGINT NOT NULL DEFAULT 0,
|
|
biz_date CHAR(8) NOT NULL,
|
|
status CHAR(1) NOT NULL DEFAULT 'R',
|
|
fee BIGINT NOT NULL DEFAULT 0,
|
|
reg_ts TIMESTAMP NOT NULL DEFAULT now(),
|
|
upd_ts TIMESTAMP,
|
|
CONSTRAINT pk_vl_ledger PRIMARY KEY (key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_vl_ddl_0005_date_status
|
|
ON vl_ledger (biz_date, status);
|
|
|
|
CREATE OR REPLACE VIEW vl_ddl_0005_daily_v AS
|
|
SELECT biz_date, status, count(*) AS cnt, sum(amount) AS total
|
|
FROM vl_ledger
|
|
GROUP BY biz_date, status;
|