如何高效查詢共享年份的記錄？

我有看起來像的數據

data
uid  id  date        value
1    a   1780-01-01  10
2    a   1780-01-02  11
3    a   1780-01-03  12
4    b   1780-01-01  10
...
999  a   1980-01-01  10

我想知道哪些ids 有 1880 年到 1900 年之間的記錄。我目前生成這個大查詢（我相信它有效）：

SELECT year1880.id FROM
(SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1880") as year1880

 INNER JOIN
 (SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1881") as year1881
 ON year1880.id = year1881.id

 INNER JOIN
 (SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1882") as year1882
 ON year1880.id = year1882.id

 ...

 INNER JOIN
 (SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1900") as year1900
 ON year1880.id = year1900.id
 ;

這是進行此查詢的最有效方法嗎？它可以以封閉的形式完成，所以我不需要用 python 生成一個巨大的查詢嗎？

數據庫是sqlite。

這應該返回所有ids在您的日期範圍內每年至少有一條記錄的記錄：

SELECT
 id
FROM
 <your table>
WHERE
 CAST(strftime("%Y", date) as decimal) BETWEEN <min year> AND <max year>
GROUP BY
 id
HAVING
 COUNT(DISTINCT strftime("%Y", date)) = <max year> - <min year> + 1
;

修訂（更優雅，絕對更強大）的答案：

我想重新考慮我之前的答案，並花了一些時間準備小提琴（可在此處獲得）以便（也許）想出更好的東西 - 我相信我已經成功了。

首先，我創建了一個從問題派生的表：

CREATE TABLE data
(
 uuid INTEGER PRIMARY KEY AUTOINCREMENT,
 id CHAR(1) NOT NULL,
 t_date DATE,
 CONSTRAINT id_t_date_uq UNIQUE (id, t_date)
);

請注意，我將問題中的列名從date更改為t_date。將SQL 關鍵字用作表名或列名是不好的做法。它可能導致潛在的混亂錯誤消息並使調試變得困難（下劃線是你的朋友）！

然後我填充了表格：

INSERT INTO data (id, t_date)
VALUES
('a', '1879-01-01'),  --- NOT IN DATE RANGE
('x', '1879-01-01'),

('a', '1881-01-01'),  -- There are 5 years in my sample range, therefore > 4
('b', '1881-01-01'),  -- should do the trick, however see discussion below
('c', '1881-01-01'),

('a', '1882-01-01'),
('b', '1882-01-01'),
('c', '1882-01-01'),
('c', '1882-06-15'),

('a', '1883-01-01'),
('b', '1883-01-01'),
('c', '1883-01-01'),

('a', '1884-01-01'),
('b', '1884-01-01'),
('c', '1884-01-01'),

('a', '1885-01-01'),
('b', '1885-01-01'),

('z', '1902-01-01');  -- NOT IN DATE RANGE

我執行的第一個查詢是這個（只是作為檢查）；

SELECT 
 id, COUNT(id)
FROM 
 data
WHERE
 CAST(strftime("%Y", t_date)AS INTEGER) BETWEEN 1880 AND 1900  -- use your SQLite function here
GROUP BY id
 HAVING COUNT(id) > 0;

結果：

id  COUNT(id)
a   5
b   5
c   5

請注意，“x”和“z”都不會出現在答案中，因為它們都不會出現在所需的年份範圍內。此外，“a”的總數為 6，但只出現了 5 個，因為第一個是在 1879 年——同樣，在範圍之外，“c”在 1882 年出現了兩次，但在不同的日期，所以花園裡的一切都很美好！

接下來，我執行了我完成的查詢（見下面的解釋）：

SELECT
 id
FROM
 data
WHERE
 CAST(strftime("%Y", t_date) as INTEGER) BETWEEN 1880 AND 1900
GROUP BY
 id
HAVING
 COUNT(DISTINCT strftime("%Y", t_date)) = 
 (
   SELECT 
     COUNT(DISTINCT CAST(strftime("%Y", t_date) AS INTEGER)) AS yr_cnt
   FROM data
   WHERE CAST(strftime("%Y", t_date) AS INTEGER) BETWEEN 1880 AND 1900
 );

結果：

id
a
b

這是正確的結果，因為 ‘a’ 和 ‘b’ 確實是id在所有 5 個相關年份中出現的僅有的兩個 s。

現在，您可能會問自己為什麼我放入（顯然是矯枉過正的）sub-SELECT來獲取COUNT(id). 那是因為"missing"數據的潛力。如果您沒有1880 年到 1900 年（含）之間每id一年的數據，那麼您的 s 將無法出現，即它們將達不到年數。

請注意，我的模擬數據僅涵蓋相關年份之間的 5 年，而實際上有 21 年 - 所以我使用的查詢COUNT(id) >= (1900 - 1880 + 1)顯然無法返回任何數據。這是一個更強大的解決方案。

引用自：https://dba.stackexchange.com/questions/274072