I have an interest in open data and being able to query that data and gain beautiful insights. One data set that would be interesting is the play whe results data. Our open data is lacking in Trinidad and I will try to contact NLCB to see if they can provide and maintain the data online. But in the meanwhile I will use randomised data to create the website and do my testing.
First thing I did was install sqlite on termux
pkg update && pkg upgrade
pkg install sqlite
sqlite3 --version
Create my database in my project folder
sqlite3 results.db
Useful commands
.exit
.quit
Exit from multiline prompt
;
SQL to create my table (create_tbl_results.sql)
CREATE TABLE DrawResults (
DrawNo INTEGER PRIMARY KEY, DrawDate DATE,
ResultNo INTEGER CHECK (ResultNo BETWEEN 1 AND 36),
DrawTime INTEGER CHECK (DrawTime BETWEEN 1 AND 4)
);
SQL to create the random data (random.sql)
WITH RECURSIVE numbers(n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 10000
)
INSERT INTO DrawResults (DrawNo, DrawDate, ResultNo, DrawTime)
SELECT
n AS DrawNo,
-- Start at 2010-01-01 and advance 1 day every 4 draws
date('2010-01-01', '+' || ((n - 1) / 4) || ' days') AS DrawDate,
-- Random number from 1 to 36
(abs(random()) % 36) + 1 AS ResultNo,
-- DrawTime cycles 1 to 4
((n - 1) % 4) + 1 AS DrawTime
FROM numbers;
SQL to query the top 10 called numbers (query_top10.sql)
SELECT
ResultNo,
COUNT(*) AS ResultCount
FROM DrawResults
GROUP BY ResultNo
ORDER BY ResultCount DESC
LIMIT 10;
Commands to run the sql (setup.sh)
rm results.db
sqlite3 results.db < create_tbl_results.sql
sqlite3 results.db < random.sql
sqlite3 results.db < query_top10.sql
Start the database from scratch in termux
chmod +x setup.sh
./setup.sh
Next up in next blog post is to create a website to display the results that will eventually be live on github pages
Update - website has been started here - https://hassan-theitguy.github.io/play-whe-queries/
Comments