2つの列の組み合わせがすべて異なるテーブルから、すべての行を取得する必要があります。つまり、同じ日に同じ価格で発生した他の売上がないすべての売上を取得したいのです。日にちと価格に基づいてユニークな売上は、アクティブなステータスに更新されます。
と考えています。
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 1)
しかし、それ以上になると頭が痛くなります。
SELECT DISTINCT a,b,c FROM t
は roughly に相当します。
SELECT a,b,c FROM t GROUP BY a,b,c
GROUP BY構文の方がより強力なので、GROUP BY構文に慣れておくと良いでしょう。
あなたのクエリの場合、私は次のようにします。
UPDATE sales
SET status='ACTIVE'
WHERE id IN
(
SELECT id
FROM sales S
INNER JOIN
(
SELECT saleprice, saledate
FROM sales
GROUP BY saleprice, saledate
HAVING COUNT(*) = 1
) T
ON S.saleprice=T.saleprice AND s.saledate=T.saledate
)
これまでの答えをまとめ、整理し、改善していくと、このような優れた問いにたどり着きます。
UPDATE sales
SET status = 'ACTIVE'
WHERE (saleprice, saledate) IN (
SELECT saleprice, saledate
FROM sales
GROUP BY saleprice, saledate
HAVING count(*) = 1
);
これはどちらよりもはるかに高速です。PostgreSQL 8.4と9.1での私のテストでは)現在受け入れられている答えの性能を10~15倍にしています。
しかし、これはまだ最適とは言えません。より良いパフォーマンスを得るためには、NOT EXISTS` (アンチ)セミジョインを使用してください。EXISTS`は標準的なSQLで、ずっと存在しており(少なくともPostgreSQL 7.2以降、この質問がされるずっと前から存在していました)、提示された要件に完全に適合します。
UPDATE sales s
SET status = 'ACTIVE'
WHERE NOT EXISTS (
SELECT FROM sales s1 -- SELECT list can be empty for EXISTS
WHERE s.saleprice = s1.saleprice
AND s.saledate = s1.saledate
AND s.id <> s1.id -- except for row itself
)
AND s.status IS DISTINCT FROM 'ACTIVE'; -- avoid empty updates. see below
db<>fiddle here 古いSQLフィドルを参照してください。
テーブルの主キーやユニークキー(例では id
)がない場合、このクエリの目的ではシステムカラム ctid
で代用できます(他の目的では使えません)。
AND s1.ctid <> s.ctid
すべてのテーブルには主キーが必要です。まだ持っていない場合は追加してください。Postgres 10+では、serial
またはIDENTITY
カラムをお勧めします。
関連があります。
EXISTS`アンチセミジョインのサブクエリは、最初の重複が見つかった時点で評価を停止することができます(それ以上調べる意味がありません)。重複が少ない基本テーブルでは、これはわずかに効率が良いだけです。重複が多い場合、これは非常に効率的になります。
すでに「status = 'ACTIVE'」となっている行に対して、この更新は何も変更しませんが、フルコストで新しい行バージョンを挿入します(細かい例外があります)。通常、これは望ましくありません。これを避けるために、上のデモのように別の WHERE
条件を追加し、さらに高速化します。
statusが
NOT NULL` と定義されている場合、以下のように単純化することができます。
AND status <> 'ACTIVE';
このクエリは、現在Joel氏によって受け入れられている回答とは異なり、NULL値を同じものとして扱いません。以下の (saleprice, saledate)
の2つの行は、(人間の目には同じに見えるが) "distinct"とみなされます。
(123, NULL)
(123, NULL)
また、SQL標準ではNULL値は比較して等しくならないので、ユニークインデックスや他のほとんどの場所でも通過します。ご覧ください。
一方、GROUP BY
やDISTINCT
、DISTINCT ON ()
では、NULL値は等しいものとして扱われます。何を実現したいかによって、適切なクエリスタイルを使用してください。この高速なクエリでも、すべての比較において =
の代わりに IS NOT DISTINCT FROM
を使用することで、NULLの比較を等しくすることができます。続きを読む
比較されるすべての列が NOT NULL
と定義されている場合は、意見の相違はありません。
このクエリの問題点は、GROUP BY句(基本的にはdistinctを使用して行います)を使用する場合、グループ化または集約関数を使用したカラムしか使用できないことです。異なる値が存在する可能性があるため、カラムidは使用できません。あなたの場合、HAVING 句を使っているので、値は常に 1 つしかありませんが、ほとんどの RDBMS はそれを認識するほど賢くありません。
しかし、これはうまくいくはずです(そしてjoinは必要ありません)。
UPDATE sales
SET status='ACTIVE'
WHERE id IN (
SELECT MIN(id) FROM sales
GROUP BY saleprice, saledate
HAVING COUNT(id) = 1
)
MINの代わりにMAXやAVGを使用することもできますが、重要なのは、一致する行が1つしかない場合に列の値を返す関数を使用することです。