zsjz-ai/sql/create_table_2.sql

-- todo 优化
DROP TABLE IF EXISTS person_call_stat;
CREATE TABLE person_call_stat
(
    personName        VARCHAR primary key,
    personPhoneCount  INTEGER,
    firstCallDateTime TIMESTAMP,
    lastCallDateTime  TIMESTAMP,
    totalCallCount    INTEGER,
    totalCallDuration INTEGER,
    outCallCount      INTEGER,
    inCallCount       INTEGER
);

DROP TABLE IF EXISTS person_deal_stat;
CREATE TABLE person_deal_stat
(
    personName          VARCHAR primary key,
    cardNoCount         INTEGER,
    otherCount          INTEGER,
    totalTransAmount    DECIMAL(18, 2),
    totalTransCount     INTEGER,
    totalInAmount       DECIMAL(18, 2),
    totalInCount        INTEGER,
    totalOutAmount      DECIMAL(18, 2),
    totalOutCount       INTEGER,
    netSettlementAmount DECIMAL(18, 2),
    firstTransDateTime  TIMESTAMP,
    lastTransDateTime   TIMESTAMP
);

DROP TABLE IF EXISTS phone_call_stat;
CREATE TABLe phone_call_stat
(
    personPhone       VARCHAR primary key,
    personName        VARCHAR,
    personCertNo      VARCHAR,
    operator          VARCHAR,
    firstCallDateTime TIMESTAMP,
    lastCallDateTime  TIMESTAMP,
    callContactCount  INTEGER,
    totalCallDuration INTEGER,
    totalCallCount    INTEGER,
    outCallDuration   INTEGER,
    outCallCount      INTEGER,
    inCallDuration    INTEGER,
    inCallCount       INTEGER
);

DROP TABLE IF EXISTS card_deal_stat;
CREATE TABLE card_deal_stat
(
    personCardNo        VARCHAR primary key,
    personName          VARCHAR,
    personCertNo        VARCHAR,
    bankCardName        VARCHAR,
    otherCount          INTEGER,
    totalTransAmount    DECIMAL(18, 2),
    totalTransCount     INTEGER,
    totalInAmount       DECIMAL(18, 2),
    totalInCount        INTEGER,
    totalOutAmount      DECIMAL(18, 2),
    totalOutCount       INTEGER,
    netSettlementAmount DECIMAL(18, 2),
    firstTransDateTime  TIMESTAMP,
    lastTransDateTime   TIMESTAMP,
    lastTransAmount     DECIMAL(18, 3)
);