选择大表中某些ID不存在于同一列中的数据。加快查询
我试图选择公司和日期不同的数据类型/ ID的数据表中的数据。选择大表中某些ID不存在于同一列中的数据。加快查询
换句话说,我想company_id, dates_id, daily_val
其中wh_calc_id = 344
如果相同company_id/dates_id
组合不存在其中wh_calc_id = 368
。
我松散下面这个例子: Select rows which are not present in other table
这些都是我的两次尝试吧:
尝试1:
SELECT distinct on (company_id, dates_id) company_id, dates_id, daily_val
FROM daily_data d1
WHERE NOT EXISTS (
SELECT 1
FROM daily_data d2
WHERE d1.company_id = d2.company_id
and d1.dates_id = d2.dates_id
and d1.wh_calc_id = 368
and d2.wh_calc_id = 368
)
and d1.wh_calc_id = 344
问题: 这是超级慢:15分钟
尝试2:[删除]
All in one的(巨)表: COMPANY_ID INT(索引), dates_id INT(索引), wh_calc_id INT(索引), daily_val数字
我打开添加索引,这将有助于速度东西了,但是什么索引?
Postgres的10
PS - 我有他们完成之前杀了两个查询,所以我真的不知道他们是否正确写入。希望我的描述有所帮助。
这不会是我想要我认为:
SELECT
d1.*
from
daily_data d1
LEFT JOIN
daily_data d2
ON
d1.company_id = d2.company_id
AND d1.dates_id = d2.dates_id
AND d2.wh_calc_id = 368
AND d1.wh_calc_id = 344
where
and d1.wh_calc_id = 344
and d2.wh_calc_id is null
我会用左手做加盟这样:
SELECT distinct on (company_id, dates_id) company_id, dates_id, daily_val FROM daily_data d1 LEFT JOIN daily_data d2 ON d1.company_id = d2.company_id and d1.dates_id = d2.dates_id and d1.wh_calc_id = 368 and d2.wh_calc_id = 368 WHERE d1.wh_calc_id = 344 AND d2.company_id IS NULL;
,并创造超过列的索引使用:
Create index on table daily_data (company_id, dates_id, wh_calc_id);
事实证明,没有做我想要的。 – mountainclimber
我会在这种情况下使用左连接:select distinct on(company_id,dates_id)company_id,dates_id,daily_val FROM daily_data d1 LEFT JOIN daily_data d2 ON d1.company_id = d2.company_id和d1.dates_id = d2.dates_id和d1.wh_calc_id = 368和d2.wh_calc_id = 368 WHERE d1.wh_calc_id = 344 AND d2.company_id IS NULL;并在要使用的列上创建索引:在表daily_data(company_id,dates_id,wh_calc_id)上创建索引; –