SQLite 範例

可直接複製貼上、查詢 GOI Bible 資料庫的 SQL 範例

以下範例假設有一個資料表(或檢視表)的結構與本專案的 v_effective_rendering 檢視表相同——每節經文一列, 欄位包含 goi(貫穿全本聖經的全域序號)、 book、chapter、verse、 lang,以及 rendering(該節經文文字)。用 sqlite3 指令列工具,或 DB Browser for SQLite 等圖形介面工具打開 GOI_bible.sqlite3,即可直接執行以下任一查詢。

1. 如何產生整本聖經

依正典順序輸出某一語言版本的每一節經文：

SELECT book, chapter, verse, rendering
FROM v_effective_rendering
WHERE lang = 'zh'
ORDER BY goi;

goi(「全域序號」)本身就已經編碼了從創世記到啟示錄正確的閱讀順序,所以單純 ORDER BY goi 即可—— 不需要額外的書卷編號對照表。

2. 如何只取出某一章,並附上節號

取出單一章,並在每一行旁附上節號,適合研讀/查閱版面：

SELECT verse, rendering
FROM v_effective_rendering
WHERE book = 'JHN' AND chapter = 3 AND lang = 'zh'
ORDER BY verse;

若想要不含節號、文字連貫的「閱讀版面」, 可省略 verse 欄位,改用 group_concat(rendering, ' ') 將同一章的經文串接起來。

3. 如何製作熱圖(heatmap)

統計某個詞在每一章出現的次數,作為熱圖的數據來源 (顏色越深表示出現次數越多)。以下範例統計「愛」：

SELECT book, chapter,
       (LENGTH(group_concat(rendering, ' '))
        - LENGTH(REPLACE(group_concat(rendering, ' '), '愛', '')))
       / LENGTH('愛') AS hits
FROM v_effective_rendering
WHERE lang = 'zh'
GROUP BY book, chapter
ORDER BY hits DESC;

結果是每章一列、附帶出現次數 hits—— 可直接餵給任何圖表套件(例如以 book/chapter 為座標的日曆式熱圖),視覺化呈現某個詞在全書中的分布密度。

4. 如何搜尋「光」與「暗」相距 10 節以內的經文

一般的 LIKE 查詢無法表達「相鄰」的概念—— 這時可以建立一個 FTS5 虛擬資料表, 將每節經文「前後 10 節」的範圍視為一份文件, 再使用 FTS5 的 NEAR 運算子：

-- 一次性設定:以每節經文前後 ±10 節作為一份 FTS5 文件
CREATE VIRTUAL TABLE verse_windows USING fts5(window_text, goi UNINDEXED);

INSERT INTO verse_windows(goi, window_text)
SELECT v.goi,
       (SELECT group_concat(w.rendering, ' ')
        FROM v_effective_rendering w
        WHERE w.lang = 'zh' AND w.goi BETWEEN v.goi - 10 AND v.goi + 10)
FROM v_effective_rendering v
WHERE v.lang = 'zh';

-- 查詢:在同一個 ±10 節範圍內,「光」與「暗」相距 10 個詞以內的經文
SELECT vr.book, vr.chapter, vr.verse, vr.rendering
FROM verse_windows fw
JOIN v_effective_rendering vr ON vr.goi = fw.goi AND vr.lang = 'zh'
WHERE fw.window_text MATCH '光 NEAR/10 暗';

verse_windows 只需建立一次(或在文字更新時重新整理)—— 建好之後,上面的 MATCH 查詢即可在整本聖經範圍內瞬間完成。

← 返回 GOI Bible