new prevalent_values table

2 files changed, 16 insertions, 0 deletions

diff --git a/sources/db/post.sql b/sources/db/post.sql
index e824c48..ca6095b 100644
--- a/sources/db/post.sql
+++ b/sources/db/post.sql
@@ -59,6 +59,13 @@ UPDATE keys SET prevalent_values=(
         )
     ) WHERE values_all < 1000;
 
+INSERT INTO prevalent_values (key, value, count, fraction)
+            SELECT t.key, t.value, t.count_all, CAST(t.count_all AS REAL) / CAST(k.count_all AS REAL) FROM tags t, keys k
+                    WHERE t.key       = k.key
+                      AND t.count_all > k.count_all / 100.0;
+
+CREATE INDEX prevalent_values_key_idx ON prevalent_values (key);
+
 ANALYZE;
 
 UPDATE source SET update_end=datetime('now');
diff --git a/sources/db/pre.sql b/sources/db/pre.sql
index 38ec051..b91e31e 100644
--- a/sources/db/pre.sql
+++ b/sources/db/pre.sql
@@ -34,6 +34,15 @@ CREATE TABLE keys (
   prevalent_values TEXT
 );
 
+DROP TABLE IF EXISTS prevalent_values;
+CREATE TABLE prevalent_values (
+  key      TEXT,
+  value    TEXT,
+  count    INTEGER,
+  fraction REAL
+);
+
+
 DROP TABLE IF EXISTS key_distributions;
 
 CREATE TABLE key_distributions (