CS50 SQL 2024 PSET3：meteorites表排序与ID分配不符check50结果

问题描述 #

我编写SQL脚本完成CS50 SQL 2024 PSET3的陨石数据清洗任务，创建meteorites表，要求按year（从旧到新）、name（字母序）排序并分配起始为1的ID，但check50检测显示排序与ID分配不符合预期：预期首条数据为Apache Junction，实际首条是Adelaide，其余步骤均通过，需排查问题。

原SQL脚本 #

DROP TABLE IF EXISTS "meteorites_temp";
DROP TABLE IF EXISTS "meteorites";

CREATE TABLE "meteorites_temp" (
    "name" TEXT,
    "id" INTEGER,
    "nametype" TEXT,
    "class" TEXT,
    "mass" REAL,
    "discovery" TEXT,
    "year" INTEGER,
    "lat" REAL,
    "long" REAL,
    PRIMARY KEY("id")
);

CREATE TABLE "meteorites" (
    "id" INTEGER,
    "name" TEXT,
    "class" TEXT,
    "mass" REAL,
    "discovery" TEXT,
    "year" INTEGER,
    "lat" REAL,
    "long" REAL,
    PRIMARY KEY("id")
);

.import --csv --skip 1 meteorites.csv meteorites_temp

UPDATE "meteorites_temp"
SET "mass" = NULL,
    "year" = NULL,
    "lat" = NULL,
    "long" = NULL
WHERE "mass" = '' OR
      "year" = '' OR
      "lat" = '' OR
      "long" = '';

UPDATE "meteorites_temp"
SET "mass" = ROUND("mass", 2),
    "lat" = ROUND("lat", 2),
    "long" = ROUND("long", 2);

DELETE FROM "meteorites_temp" WHERE "nametype" = 'Relict';

INSERT INTO "meteorites" ("name", "class", "mass", "discovery", "year", "lat", "long")
SELECT "name", "class", "mass", "discovery", "year", "lat", "long"
FROM "meteorites_temp"
ORDER BY "year", "name";

DROP TABLE "meteorites_temp";

问题根源 #

1. NULL值排序逻辑不符预期：SQLite默认将NULL视为比任何非NULL值都小，所以year为NULL的记录会排在所有有有效年份的记录前面。Adelaide的year字段为NULL，而Apache Junction有有效且较早的年份，导致原排序中Adelaide被优先插入。
2. ID分配未严格绑定排序顺序：虽然INSERT时加了ORDER BY，但未显式生成连续ID，依赖SQLite自动递增主键的机制可能因临时表原有数据干扰，无法保证ID与排序顺序完全对应。

解决方法 #

修改INSERT语句，通过ROW_NUMBER()函数显式生成从1开始的连续ID，同时指定year NULLS LAST将无年份的记录排在最后，确保排序逻辑符合要求：

INSERT INTO "meteorites" ("id", "name", "class", "mass", "discovery", "year", "lat", "long")
SELECT 
    ROW_NUMBER() OVER (ORDER BY "year" NULLS LAST, "name"),
    "name", "class", "mass", "discovery", "year", "lat", "long"
FROM "meteorites_temp"
ORDER BY "year" NULLS LAST, "name";

说明 #

• ROW_NUMBER() OVER (ORDER BY "year" NULLS LAST, "name")：生成严格遵循排序顺序的连续ID，从1开始递增。
• "year" NULLS LAST：强制将year为NULL的记录排在所有有有效年份的记录之后，符合“按year从旧到新”的要求。

内容的提问来源于stack exchange，提问作者Angel