51 lines
1.7 KiB
SQL
51 lines
1.7 KiB
SQL
|
-- Word tracking & sentiment analytics for NAS documents
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS document_analytics (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
storage_path TEXT UNIQUE NOT NULL,
|
||
|
|
filename VARCHAR(512),
|
||
|
|
doc_type VARCHAR(64) DEFAULT 'general',
|
||
|
|
language VARCHAR(16),
|
||
|
|
word_count INT DEFAULT 0,
|
||
|
|
unique_lemmas INT DEFAULT 0,
|
||
|
|
sentence_count INT DEFAULT 0,
|
||
|
|
sentiment_compound FLOAT,
|
||
|
|
sentiment_positive FLOAT,
|
||
|
|
sentiment_negative FLOAT,
|
||
|
|
sentiment_neutral FLOAT,
|
||
|
|
sentiment_subjectivity FLOAT,
|
||
|
|
sentiment_label VARCHAR(32),
|
||
|
|
extraction_method VARCHAR(64) DEFAULT 'standard',
|
||
|
|
file_sig VARCHAR(64),
|
||
|
|
analyzed_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
metadata JSONB DEFAULT '{}'
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS document_word_counts (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
storage_path TEXT NOT NULL,
|
||
|
|
lemma VARCHAR(128) NOT NULL,
|
||
|
|
token VARCHAR(128),
|
||
|
|
pos_tag VARCHAR(16),
|
||
|
|
count INT DEFAULT 1,
|
||
|
|
is_stopword BOOLEAN DEFAULT FALSE,
|
||
|
|
language VARCHAR(16),
|
||
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
UNIQUE (storage_path, lemma)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_doc_word_lemma ON document_word_counts (lemma);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_doc_word_path ON document_word_counts (storage_path);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_doc_word_stop ON document_word_counts (is_stopword);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_doc_analytics_sentiment ON document_analytics (sentiment_label);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_doc_analytics_analyzed ON document_analytics (analyzed_at DESC);
|
||
|
|
|
||
|
|
CREATE OR REPLACE VIEW global_word_frequency AS
|
||
|
|
SELECT
|
||
|
|
lemma,
|
||
|
|
MAX(token) AS sample_token,
|
||
|
|
SUM(count) AS total_count,
|
||
|
|
COUNT(DISTINCT storage_path) AS document_count
|
||
|
|
FROM document_word_counts
|
||
|
|
GROUP BY lemma;
|