Temuduga SQL: Cari Tiga Produk Teratas mengikut Kategori

Arahan dan Konteks yang Berkenaan

Menggunakan PostgreSQL, kembalikan tiga tahap hasil produk berbeza teratas dalam setiap kategori untuk S2 2026, termasuk setiap produk yang seri pada tahap ketiga. Nyatakan kontrak data, tulis SQL, dan terangkan mengapa ROW_NUMBER, RANK, dan DENSE_RANK menghasilkan keputusan yang berbeza.

Skema adalah seperti berikut:

orders( order_id bigint primary key, ordered_at timestamptz, status text )

order_items( order_id bigint, category_id bigint, product_id bigint, quantity integer, unit_price numeric(12, 2) )

Gunakan kontrak yang dipersetujui ini: hitung hanya pesanan COMPLETED; takrifkan suku tahun sebagai selang separuh terbuka UTC [2026-04-01, 2026-07-01); hasil produk adalah jumlah quantity * unit_price bagi item baris yang layak; bayaran balik berada di luar model yang diberikan; dan "tiga teratas" bermaksud tiga tahap hasil berbeza tertinggi setiap kategori, jadi setiap produk yang seri pada tahap ketiga mesti dikembalikan. Produk tanpa jualan yang layak tidak akan muncul, dan kategori dengan kurang daripada tiga tahap mengembalikan semua tahap yang ada.

Soalan tahap sederhana ini sesuai untuk peranan penganalisis data, jurutera data, dan bahagian belakang yang memerlukan SQL analitik. Bahan temuduga SQL 2026 awam masih menyenaraikan top-N-per-kumpulan, fungsi tetingkap, dan tingkah laku seri sebagai latihan eksplisit. Tiada atribusi syarikat yang boleh disahkan, jadi ia dianggap sebagai soalan temuduga SQL yang mewakili.

Perkara yang Dinilai oleh Penemuduga

Isyarat pertama ialah sama ada anda menyatakan butiran output. Satu baris order_items adalah satu item baris, tetapi sasarannya adalah satu baris bagi setiap pasangan kategori-produk. Menggredkan item baris mentah memberikan satu produk beberapa kedudukan. SQL yang sah dari segi sintaks masih boleh menjawab soalan perniagaan yang salah.

Isyarat kedua ialah sama ada anda menterjemahkan "tiga teratas" kepada semantik seri yang eksplisit. ROW_NUMBER, RANK, dan DENSE_RANK semuanya boleh menyusun baris dalam kategori, tetapi ia menjawab soalan yang berbeza. Calon yang kuat terlebih dahulu bertanya sama ada keputusan memerlukan tepat tiga baris, gred pertandingan, atau tiga tahap hasil yang berbeza.

Isyarat ketiga ialah pengetahuan tentang susunan penilaian logik SQL. Fungsi tetingkap berjalan selepas WHERE, GROUP BY, HAVING, dan pengagregatan biasa. Agregat hasil produk terlebih dahulu, gredkan pada peringkat seterusnya, dan tapis gred dalam pertanyaan luar. PostgreSQL tidak boleh menggunakan alias tetingkap dalam klausa WHERE peringkat pertanyaan yang sama.

Isyarat terakhir ialah kebolehsahihan dan kebolehselenggaraan. Jawapan yang kuat mentakrifkan sempadan masa, status yang layak, jenis wang, susunan pembentangan yang deterministik, dan lekapan ujian. Pada skala besar, ia terlebih dahulu mengurangkan baris yang memasuki peringkat pengredan berbanding menawarkan "tambah indeks" sebagai pemulihan yang tidak disokong.

Soalan untuk Dijelaskan Sebelum Menjawab

Adakah "tiga teratas" bermaksud tiga baris, tiga kedudukan pertandingan, atau tiga nilai berbeza? Gunakan ROW_NUMBER untuk tiga baris, RANK untuk pengredan pertandingan, dan DENSE_RANK untuk keperluan ini: tiga tahap hasil berbeza dengan seri yang dipelihara.
Apakah peristiwa dan zon masa yang mengaitkan hasil kepada suatu tempoh? Masa pesanan, pembayaran, penyelesaian, dan bayaran balik menghasilkan laporan yang berbeza. Arahan ini menggunakan orders.ordered_at dalam UTC dan selang separuh terbuka yang tidak bertindih.
Status manakah yang layak? Memasukkan pesanan dibatalkan, ujian, atau gagal akan menggembungkan hasil. Kontrak ini mengira hanya COMPLETED.
Di mana bayaran balik, diskaun, cukai, dan mata wang? Model yang diberikan hanya mengandungi kuantiti dan harga unit transaksi. Jika jadual bayaran balik atau berbilang mata wang ditambah, kira hasil bersih atau tukar kepada satu mata wang sebelum membuat pengredan.
Bolehkah satu produk tergolong dalam berbilang kategori? Pertanyaan ini menganggap (category_id, product_id) pada item baris sebagai kunci fakta. Menggabungkan dimensi produk hari ini boleh menulis semula atribusi kategori sejarah; gunakan syot kilat masa pesanan atau dimensi bertarikh efektif apabila sejarah itu penting.
Adakah produk berjualan sifar perlu muncul? Ia tidak muncul di sini. Jika diperlukan, gabungkan agregat ke kiri dengan set kategori-produk yang lengkap dan takrifkan sama ada sifar mengambil bahagian dalam tiga tahap teratas.
Mestikah susunan output bersifat deterministik? Gred hanya mengikut hasil; menambah product_id pada tetingkap pengredan akan memusnahkan seri. Stabilkan pembentangan secara berasingan dengan ORDER BY category_id, revenue_rank, product_id.

Rangka Kerja Jawapan 30 Saat

"Saya akan mengagregat pesanan S2 UTC yang selesai kepada satu baris setiap kategori dan produk, kemudian gunakan DENSE_RANK mengikut kategori dan hasil menurun kerana keperluan adalah tiga tahap berbeza dengan seri. CTE mengira gred, pertanyaan luar menapis revenue_rank <= 3, dan susunan akhir hanya menstabilkan paparan. Saya akan menguji seri, sempadan suku tahun, pesanan dibatalkan, dan kategori dengan kurang daripada tiga tahap."

Jawapan yang lengkap juga harus menerangkan mengapa pengagregatan mendahului pengredan, bagaimana ketiga-tiga fungsi pengredan berbeza, dan cara mengesahkan kontrak pelaporan dan pelan pelaksanaan pada skala besar.

Jawapan Mendalam Langkah demi Langkah

Langkah 1: Nyatakan Soalan Perniagaan sebagai Butiran dan Invarian

Kunci hasil bukan order_id; ia adalah (category_id, product_id). Setiap kunci mesti muncul sekali dalam peringkat agregat, dan revenue mesti sama dengan jumlah semua amaun item baris yang layak bagi kunci tersebut. Pengredan menambah tahap tempatan kategori tanpa mengubah butiran.

Nyatakan tiga invarian sebelum menulis sintaks: setiap item baris yang layak menyumbang tepat sekali; produk yang sama merentasi pesanan berbeza digabungkan; dan revenue_rank sesuatu kategori bergantung hanya pada hasil dalam kategori tersebut. Invarian ini mendedahkan dengan cepat gabungan pendua, pengredan item baris, dan pengredan global.

Langkah 2: Tapis Fakta Sebelum Mengagregat ke Butiran Produk

Gunakan >= sebagai permulaan dan < sebagai permulaan suku tahun seterusnya. Tidak seperti BETWEEN atau 2026-06-30 23:59:59, selang separuh terbuka tidak terlepas ketepatan cap masa yang lebih tinggi dan bergabung dengan bersih pada suku tahun seterusnya. +00 yang eksplisit pada literal timestamptz memastikan kontrak bebas daripada zon masa sesi pangkalan data.

Selepas menggabungkan, tapis status dan masa, kemudian kumpulkan mengikut category_id, product_id. Dalam model PostgreSQL ini, quantity * unit_price kekal sebagai ungkapan numerik tepat, dan SUM memelihara aritmetik wang yang tepat. Jangan hantar ke titik terapung untuk kemudahan. Laporan hasil pengeluaran juga memerlukan semantik mata wang dan bayaran balik, tetapi lajur yang diberikan tidak dapat menghasilkannya.

Langkah 3: Pilih Fungsi Pengredan daripada Kontrak Seri

Andaikan satu kategori mempunyai hasil produk 100, 100, 90, 80:

ROW_NUMBER menghasilkan 1, 2, 3, 4. Tanpa kunci tambahan, dua baris 100 mempunyai susunan relatif yang tidak ditetapkan. Contoh ini mengembalikan kedua-dua baris 100 dan satu baris 90, tetapi seri yang melintasi sempadan baris ketiga akan dipotong secara sewenang-wenangnya. Ia menjamin tiga baris, bukan seri yang lengkap.
RANK menghasilkan 1, 1, 3, 4. Menapis pada 3 mengembalikan hasil 100 dan 90, hanya dua tahap berbeza.
DENSE_RANK menghasilkan 1, 1, 2, 3. Menapis pada 3 mengembalikan semua empat produk dan tepat tiga tahap berbeza.

Oleh itu, arahan ini memerlukan DENSE_RANK. Tetingkap ORDER BY mesti mengandungi hanya revenue DESC. Menambah product_id bermaksud baris hasil yang sama bukan lagi rakan sebaya, menggantikan secara senyap "pelihara seri" dengan susunan buatan.

Langkah 4: Asingkan Pengagregatan, Pengredan, dan Penapisan dengan Dua CTE

Pertanyaan PostgreSQL yang lengkap adalah:

WITH product_revenue AS ( SELECT oi.category_id, oi.product_id, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders AS o JOIN order_items AS oi ON oi.orderid = o.orderid WHERE o.status = 'COMPLETED' AND o.ordered_at >= TIMESTAMPTZ '2026-04-01 00:00:00+00' AND o.ordered_at < TIMESTAMPTZ '2026-07-01 00:00:00+00' GROUP BY oi.categoryid, oi.productid ), ranked AS ( SELECT category_id, product_id, revenue, DENSE_RANK() OVER ( PARTITION BY category_id ORDER BY revenue DESC ) AS revenue_rank FROM product_revenue ) SELECT categoryid, productid, revenue, revenue_rank FROM ranked WHERE revenue_rank <= 3 ORDER BY categoryid, revenuerank, product_id;

Peringkat pertama mengurangkan banyak item pesanan kepada fakta produk. Peringkat kedua menyusun hanya set produk dalam setiap kategori. Pertanyaan luar kemudiannya boleh melihat dan menapis hasil tetingkap. Peringkat-peringkat ini mencerminkan kontrak metrik, kontrak pengredan, dan kontrak output, supaya butiran dan bilangan baris mereka boleh diperiksa secara bebas.

Langkah 5: Buktikan Ketepatan Bukan Sekadar Menunjukkan Sintaks

Bagi mana-mana kategori, peringkat pertama mencipta satu jumlah setiap produk. PARTITION BY category_id mengecualikan setiap kategori lain daripada perbandingan, dan ORDER BY revenue DESC mentakrifkan jumlah yang sama sebagai satu kumpulan rakan sebaya. DENSE_RANK menomborkan kumpulan rakan sebaya secara berturutan tanpa jurang, jadi <= 3 memilih tepat tiga jumlah berbeza tertinggi dan setiap produk yang tergolong dalam jumlah tersebut.

ORDER BY akhir tidak menjejaskan gred; ia hanya menyusun baris yang dikembalikan. Perbezaan ini penting: susunan dalam tetingkap mentakrifkan gred perniagaan, manakala susunan di penghujung mentakrifkan pembentangan yang boleh diulang. Ia boleh menggunakan kunci yang berbeza.

Langkah 6: Kaitkan Tuntutan Prestasi kepada Bukti

Jika R item baris yang layak menjadi G kumpulan kategori-produk, kerja logik mengimbas dan mengagregat R baris, kemudian menyusun G baris dalam kategori. Penyusunan boleh dinyatakan sebagai jumlah G_c log G_c merentasi partition kategori, tetapi PostgreSQL boleh memilih pengagregatan cincang, pengagregatan susunan, pelaksanaan selari, atau tumpahan cakera. Ungkapan tersebut bukan janji tentang pelan fizikal.

Gunakan EXPLAIN (ANALYZE, BUFFERS) pada replika skala pengeluaran untuk memeriksa baris yang ditapis, strategi gabungan, baris agregat, memori penyusunan, dan fail sementara. orders(status, ordered_at, order_id) dan order_items(order_id) mungkin membantu penapisan dan penggabungan, bergantung pada keselectifan status, taburan, dan pembahagian. Pembahagian masa boleh memangkas jadual fakta yang besar. Untuk laporan yang kerap, pertimbangkan agregat kategori-produk harian yang diselaraskan. Pra-pengagregatan memperdagangkan kesegaran, isian semula, dan kerumitan pembetulan dengan kelajuan pertanyaan; "cipta paparan terwujud" bukan reka bentuk yang lengkap.

Langkah 7: Buktikan Semantik dengan Data Kecil dan Kos dengan Data Besar

Lekapan ketepatan minimum merangkumi kategori 10 dengan hasil 100, 100, 90, 80, 70; jangkakan empat produk pertama dengan gred 1, 1, 2, 3. Kategori 20 hanya mempunyai 50, 40; jangkakan kedua-duanya. Sertakan satu pesanan dibatalkan, yang tidak boleh menyumbang, dan satu pesanan tepat pada 2026-07-01 00:00:00+00, yang mesti dikecualikan.

Tambah beberapa pesanan dan item baris untuk produk yang sama bagi memastikan ia menghasilkan satu jumlah. Berikan ID produk yang berbeza jumlah yang sama untuk mengesahkan susunan akhir yang stabil tetapi gred yang sama. Pengesahan skala kemudiannya memeriksa partition yang diimbas, kardinaliti agregat, tumpahan susun, masa berlalu, dan sumber kemuncak. Output yang betul dan kos yang boleh diterima adalah badan bukti yang berasingan.

Contoh Jawapan Berkualiti Tinggi

"Saya akan terlebih dahulu menjelaskan tingkah laku seri, atribusi masa, dan status yang layak. Keperluan adalah tiga tahap hasil berbeza tertinggi setiap kategori dengan semua seri tahap ketiga, jadi saya akan menggunakan DENSE_RANK; saya akan menggunakan ROW_NUMBER hanya untuk tepat tiga baris.

Butiran sasaran adalah satu baris setiap kategori dan produk, manakala butiran sumber adalah satu item pesanan. CTE pertama saya oleh itu menggabungkan pesanan kepada item, menyimpan pesanan COMPLETED dalam S2 UTC, dan mengagregat SUM(quantity * unit_price) mengikut category_id, product_id. Saya menggunakan 1 April inklusif dan 1 Julai eksklusif supaya suku tahun bersebelahan tidak bertindih.

CTE kedua menggunakan DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC). Saya tidak menambah product_id pada tetingkap tersebut kerana ia akan memisahkan rakan sebaya yang berpendapatan sama. PostgreSQL tidak dapat menapis hasil tetingkap dalam WHERE peringkat pertanyaan yang sama, jadi pertanyaan luar menggunakan revenue_rank <= 3 dan kemudian menyusun mengikut kategori, gred, dan ID produk untuk paparan yang stabil.

Hasil 100, 100, 90, 80 menjadi gred 1, 1, 2, 3, jadi keempat-empat produk dikembalikan. Saya juga akan menguji pesanan dibatalkan, sempadan kanan suku tahun, item baris berulang untuk satu produk, dan kategori dengan kurang daripada tiga tahap. Pada skala besar, saya akan mengukur sejauh mana penapisan dan pengagregatan mengurangkan R baris sumber sebelum menggunakan pelan pelaksanaan untuk mewajarkan pemangkasan partition, indeks, penalaan tumpahan, atau pra-pengagregatan."

Jawapan ini menetapkan semantik perniagaan sebelum sintaks, kemudian menyediakan struktur pertanyaan, bukti ketepatan, dan laluan prestasi yang terukur. Ia tidak menganggap nama fungsi atau cadangan indeks yang tidak diuji sebagai jawapan.

Kesilapan Biasa

Menggredkan item pesanan secara langsung → satu produk menduduki beberapa kedudukan dan butiran output adalah salah → agregat mengikut kategori dan produk sebelum membuat pengredan.
Menggunakan ORDER BY ... LIMIT 3 global → ia mengembalikan tiga baris untuk keseluruhan set data → bahagikan pengredan mengikut category_id.
Memilih ROW_NUMBER tanpa kontrak seri → produk yang seri boleh dipotong secara sewenang-wenangnya → takrifkan tiga baris, kedudukan pertandingan, atau tiga nilai berbeza terlebih dahulu.
Menambah product_id pada susunan DENSE_RANK → hasil yang sama berhenti menjadi rakan sebaya → gred hanya pada kunci gred perniagaan dan stabilkan paparan dalam ORDER BY akhir.
Menapis revenue_rank dalam WHERE yang sama → hasil tetingkap tidak wujud pada peringkat logik tersebut → gred dalam CTE atau subpertanyaan dan tapis di luar.
Menamatkan suku tahun pada 30 Jun 23:59:59 → ketepatan cap masa yang lebih tinggi boleh terlepas dan tempoh bersebelahan menjadi janggal → gunakan [start, next_start).
Menggabungkan dimensi kategori hari ini tanpa semantik sejarah → pengkategorian semula produk menulis semula sejarah → gunakan syot kilat masa pesanan atau dimensi bertarikh efektif dan nyatakan kontrak.
Hanya menawarkan indeks → selectivity yang rendah mungkin menjadikannya tidak berguna, dan ia tidak dapat membaiki butiran yang salah → ukur kardinaliti, pelan, tumpahan, dan kesesakan sebenar.
Mengumpulkan wang dalam titik terapung → pembundaran boleh mengubah kumpulan seri → kekalkan jenis numerik tepat dan takrifkan peraturan mata wang dan hasil bersih.

Soalan Susulan dan Respons

Susulan 1: Bagaimana jika produk memerlukan tepat tiga baris setiap kategori?

Gunakan ROW_NUMBER dan takrifkan susunan sekunder deterministik yang boleh dijelaskan untuk hasil yang sama, seperti product_id ASC atau peraturan unit-terjual atau tarikh-pelancaran yang diminta secara eksplisit. Peraturan itu sengaja memutuskan seri, jadi ia tergolong dalam kontrak output. Kategori dengan kurang daripada tiga produk masih mengembalikan lebih sedikit baris melainkan ruang tempat diminta secara eksplisit.

Susulan 2: Bagaimana jika "tiga teratas" menggunakan pengredan pertandingan?

Gunakan RANK. Hasil 100, 100, 90, 80 menerima 1, 1, 3, 4, dan menapis pada 3 mengembalikan tahap 100 dan 90. Ia memelihara jurang selepas seri, sepadan dengan "produk seterusnya adalah ketiga." Itu berbeza daripada meminta tiga nilai berbeza teratas dengan DENSE_RANK.

Susulan 3: Bolehkah anda mengelakkan CTE dan menapis gred dalam HAVING?

Anda tidak boleh menapis hasil tetingkap dalam HAVING atau WHERE peringkat pertanyaan yang sama, kerana fungsi tetingkap adalah secara logik lebih lewat. Subpertanyaan yang setara berfungsi. Sesetengah gudang data menyokong QUALIFY, tetapi PostgreSQL 18 tidak menyokongnya. CTE di sini menjadikan sempadan pengagregatan, pengredan, dan penapisan eksplisit.

Susulan 4: Bagaimana jika bayaran balik berlaku pada suku tahun berikutnya?

Takrifkan kontrak perakaunan terlebih dahulu. Laporan atribusi pesanan boleh menyatakan semula suku tahun pesanan asal; laporan aliran tunai boleh merekodkan peristiwa negatif dalam suku tahun bayaran balik. Itu memerlukan masa peristiwa dan jadual fakta yang berbeza. Jangan tolak jadual bayaran balik yang tidak mempunyai semantik amaun dan masa peristiwa; modelkan fakta pesanan dan bayaran balik yang boleh dikesan dan nyatakan polisi penyataan semula atau pelarasan.

Susulan 5: Bagaimana anda mengendalikan satu bilion item baris suku tahunan?

Pangkas partition tarikh dan tapis status sebelum membuat pengredan, kemudian periksa indeks kunci gabungan, paralelisme agregat, dan tumpahan susunan. Jika laporan adalah kerap dan boleh bertolak ansur dengan kelewatan, kekalkan agregat kategori-produk harian yang tepat dan jumlahkan untuk suku tahun. Pesanan lewat, bayaran balik, dan pembetulan memerlukan isian semula idempotan dan penyelarasan kepada fakta mentah. Hanya pelan pelaksanaan dan beban kerja yang mewakili boleh memilih antara pengindeksan, pembahagian, dan pra-pengagregatan.

Susulan 6: Bagaimana anda mengesan gabungan yang menggandakan hasil?

Semak butiran dan pemuliharaan pada setiap peringkat: sama ada bilangan baris merentasi gabungan item yang layak sepadan dengan kardinaliti yang dijangkakan, sama ada orders.order_id adalah unik, dan sama ada jumlah agregat produk sama dengan jumlah amaun item yang ditapis. Tambah contoh balas dengan dua item dalam satu pesanan dan kunci dimensi pendua. Jika dimensi tidak unik, pilih satu versi bertarikh efektif sebelum menggabungkan; DISTINCT akhir hanya menyembunyikan sumbangan yang digandakan.