SQL Điều Tra Dữ Liệu: Bộ Query Kiểm Tra, So Sánh Và Thống Kê

customers       : customer_id, email, name, phone, created_at, deleted_at
products        : product_id, product_code, name, price, status, created_at
product_prices  : id, product_id, price, is_current, valid_from
orders          : order_id, order_no, customer_id, status, total_amount,
                  ordered_at, shipped_at, deleted_at
order_items     : item_id, order_id, product_id, qty, unit_price
payments        : payment_id, order_id, amount, paid_at
invoices        : invoice_id, invoice_no, order_id, issued_at
invoice_counters: year, last_no              -- bộ đếm sinh số hóa đơn

SELECT
  (SELECT COUNT(*) FROM orders)       AS orders,
  (SELECT COUNT(*) FROM order_items)  AS order_items,
  (SELECT COUNT(*) FROM payments)     AS payments;

orders   order_items  payments
125430   389112       118207

SELECT
  (SELECT <giá trị> FROM <bảng 1> WHERE ...) AS ten_cot_1,
  (SELECT <giá trị> FROM <bảng 2> WHERE ...) AS ten_cot_2,
  ...;

SELECT
  (SELECT COUNT(*) FROM orders)                                   AS total,
  (SELECT COUNT(*) FROM orders WHERE status = 'pending')          AS pending,
  (SELECT COUNT(*) FROM orders WHERE status = 'paid')             AS paid,
  (SELECT COUNT(*) FROM orders WHERE status = 'cancelled')        AS cancelled,
  (SELECT COUNT(*) FROM orders WHERE deleted_at IS NOT NULL)      AS soft_deleted;

SELECT
  COUNT(*)                         AS total,
  SUM(status = 'pending')          AS pending,
  SUM(status = 'paid')             AS paid,
  SUM(status = 'cancelled')        AS cancelled,
  SUM(deleted_at IS NOT NULL)      AS soft_deleted
FROM orders;

SELECT
  (SELECT COUNT(*) FROM orders)                         AS orders,
  (SELECT COUNT(DISTINCT order_id) FROM order_items)    AS orders_with_items,
  (SELECT COUNT(DISTINCT order_id) FROM payments)       AS orders_with_payment;

orders   orders_with_items  orders_with_payment
125430   125428             118207

SELECT
  (SELECT MAX(ordered_at) FROM orders)         AS last_order,
  (SELECT MAX(paid_at)    FROM payments)       AS last_payment,
  (SELECT MAX(created_at) FROM customers)      AS last_customer,
  NOW()                                        AS now_time;

last_order           last_payment         last_customer        now_time
2026-11-04 09:58:12  2026-11-03 23:59:40  2026-11-04 09:41:05  2026-11-04 10:00:03

SELECT t.*,
       t.orders - t.orders_with_items  AS orders_without_items,
       t.orders = t.orders_with_items  AS ok
FROM (
  SELECT
    (SELECT COUNT(*) FROM orders)                       AS orders,
    (SELECT COUNT(DISTINCT order_id) FROM order_items)  AS orders_with_items
) t;

orders   orders_with_items  orders_without_items  ok
125430   125428             2                     0

SELECT t.*, t.dev_orders - t.stg_orders AS diff
FROM (
  SELECT
    (SELECT COUNT(*) FROM app_dev.orders)  AS dev_orders,
    (SELECT COUNT(*) FROM app_stg.orders)  AS stg_orders
) t;

SELECT 'orders' AS tbl,
       (SELECT COUNT(*) FROM app_dev.orders) AS dev,
       (SELECT COUNT(*) FROM app_stg.orders) AS stg
UNION ALL
SELECT 'order_items',
       (SELECT COUNT(*) FROM app_dev.order_items),
       (SELECT COUNT(*) FROM app_stg.order_items)
UNION ALL
SELECT 'payments',
       (SELECT COUNT(*) FROM app_dev.payments),
       (SELECT COUNT(*) FROM app_stg.payments);

tbl          dev      stg
orders       125430   125430
order_items  389112   389101   ← lệch 11 dòng
payments     118207   118207

SET @order_id = (SELECT order_id FROM orders WHERE order_no = 'ORD-000123');

SELECT
  @order_id                                                          AS order_id,
  (SELECT status       FROM orders      WHERE order_id = @order_id)  AS status,
  (SELECT total_amount FROM orders      WHERE order_id = @order_id)  AS total_amount,
  (SELECT COUNT(*)     FROM order_items WHERE order_id = @order_id)  AS item_cnt,
  (SELECT SUM(qty * unit_price) FROM order_items WHERE order_id = @order_id) AS items_total,
  (SELECT COUNT(*)     FROM payments    WHERE order_id = @order_id)  AS payment_cnt,
  (SELECT SUM(amount)  FROM payments    WHERE order_id = @order_id)  AS paid_amount;

order_id  status  total_amount  item_cnt  items_total  payment_cnt  paid_amount
123       paid    450000        3         450000       2            900000

SELECT c.customer_id,
       c.email,
       (SELECT COUNT(*)        FROM orders o WHERE o.customer_id = c.customer_id) AS order_cnt,
       (SELECT MAX(ordered_at) FROM orders o WHERE o.customer_id = c.customer_id) AS last_order,
       (SELECT SUM(total_amount) FROM orders o
         WHERE o.customer_id = c.customer_id AND o.status <> 'cancelled')        AS total_spent
FROM customers c
WHERE c.created_at >= '2026-10-01'
LIMIT 50;

SELECT 'orders'           AS metric, COUNT(*) AS value FROM orders
UNION ALL
SELECT 'order_items',                COUNT(*)          FROM order_items
UNION ALL
SELECT 'payments',                   COUNT(*)          FROM payments
UNION ALL
SELECT 'customers active',           COUNT(*)          FROM customers WHERE deleted_at IS NULL;

SELECT
  (SELECT COUNT(*) FROM orders)      AS orders,
  (SELECT COUNT(*) FROM order_items) AS order_items,
  (SELECT COUNT(*) FROM payments)    AS payments\G

*************************** 1. row ***************************
     orders: 125430
order_items: 389112
   payments: 118207

SELECT *
FROM      (SELECT COUNT(*) AS orders, MAX(ordered_at) AS last_order FROM orders)   o
CROSS JOIN (SELECT COUNT(*) AS payments, MAX(paid_at) AS last_paid FROM payments)  p;

-- Lỗi nếu khách hàng có nhiều đơn
SELECT (SELECT order_no FROM orders WHERE customer_id = 10) AS order_no;

-- Sửa: dùng hàm tổng hợp, hoặc ORDER BY + LIMIT 1
SELECT (SELECT MAX(order_no) FROM orders WHERE customer_id = 10)                AS latest_no,
       (SELECT order_no FROM orders WHERE customer_id = 10
        ORDER BY ordered_at DESC LIMIT 1)                                      AS last_order_no,
       (SELECT GROUP_CONCAT(order_no) FROM orders WHERE customer_id = 10)       AS all_order_no;

SELECT t.*, t.total_amount = t.paid_amount AS ok        -- ok = NULL nếu chưa thanh toán
FROM (SELECT
        (SELECT total_amount FROM orders   WHERE order_id = 123)          AS total_amount,
        (SELECT COALESCE(SUM(amount), 0) FROM payments WHERE order_id = 123) AS paid_amount
     ) t;

START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT (SELECT COUNT(*) FROM orders) AS orders,
       (SELECT COUNT(*) FROM order_items) AS order_items;
COMMIT;

invoice_counters : year, last_no          -- mỗi năm một dòng
invoices         : invoice_id, invoice_no, order_id, issued_at
                   UNIQUE KEY uk_invoice_no (invoice_no)

SELECT (SELECT last_no FROM invoice_counters WHERE year = 2026)          AS counter,
       (SELECT MAX(CAST(SUBSTRING_INDEX(invoice_no, '-', -1) AS UNSIGNED))
          FROM invoices WHERE invoice_no LIKE 'INV-2026-%')              AS max_used,
       (SELECT last_no FROM invoice_counters WHERE year = 2026)
         >= (SELECT MAX(CAST(SUBSTRING_INDEX(invoice_no, '-', -1) AS UNSIGNED))
               FROM invoices WHERE invoice_no LIKE 'INV-2026-%')         AS ok;

counter  max_used  ok
450      873       0     ← bộ đếm tụt 423 số, đây là nguyên nhân bug

SELECT invoice_no, issued_at
FROM invoices
WHERE invoice_no LIKE 'INV-2026-%'
  AND CAST(SUBSTRING_INDEX(invoice_no, '-', -1) AS UNSIGNED)
      > (SELECT last_no FROM invoice_counters WHERE year = 2026)
ORDER BY invoice_no
LIMIT 20;

SELECT 'bộ đếm hóa đơn >= số đã dùng' AS check_name,
       (SELECT last_no FROM invoice_counters WHERE year = 2026)
         >= (SELECT MAX(CAST(SUBSTRING_INDEX(invoice_no, '-', -1) AS UNSIGNED))
               FROM invoices WHERE invoice_no LIKE 'INV-2026-%') AS ok
UNION ALL
SELECT 'không có email khách hàng trùng',
       NOT EXISTS (SELECT 1 FROM customers WHERE deleted_at IS NULL
                   GROUP BY email HAVING COUNT(*) > 1)
UNION ALL
SELECT 'mọi order_item trỏ tới sản phẩm tồn tại',
       NOT EXISTS (SELECT 1 FROM order_items oi
                   WHERE NOT EXISTS (SELECT 1 FROM products p
                                     WHERE p.product_id = oi.product_id))
UNION ALL
SELECT 'qty luôn > 0',
       NOT EXISTS (SELECT 1 FROM order_items WHERE qty <= 0);

check_name                                ok
bộ đếm hóa đơn >= số đã dùng              1
không có email khách hàng trùng           1
mọi order_item trỏ tới sản phẩm tồn tại   0   ← cần điều tra
qty luôn > 0                              1

SELECT email,
       COUNT(*)                                        AS cnt,
       GROUP_CONCAT(customer_id ORDER BY customer_id)  AS customer_ids
FROM customers
WHERE deleted_at IS NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC
LIMIT 50;

SELECT order_id, product_id, COUNT(*) AS cnt
FROM order_items
GROUP BY order_id, product_id
HAVING COUNT(*) > 1;

SELECT *
FROM (
  SELECT oi.*,
         COUNT(*)     OVER (PARTITION BY order_id, product_id) AS dup_cnt,
         ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY item_id) AS rn
  FROM order_items oi
) t
WHERE dup_cnt > 1
ORDER BY order_id, product_id, rn;

SELECT LOWER(TRIM(email))          AS normalized,
       COUNT(DISTINCT BINARY email) AS raw_variants,
       GROUP_CONCAT(DISTINCT email) AS variants
FROM customers
GROUP BY normalized
HAVING raw_variants > 1;

SELECT oi.item_id, oi.order_id, oi.product_id
FROM order_items oi
LEFT JOIN products p ON p.product_id = oi.product_id
WHERE p.product_id IS NULL
LIMIT 100;

SELECT oi.item_id, oi.order_id, oi.product_id
FROM order_items oi
WHERE NOT EXISTS (
  SELECT 1 FROM products p WHERE p.product_id = oi.product_id
)
LIMIT 100;

SELECT o.order_no, o.status, o.ordered_at
FROM orders o
WHERE o.status = 'paid'
  AND o.deleted_at IS NULL
  AND NOT EXISTS (SELECT 1 FROM payments pm WHERE pm.order_id = o.order_id)
ORDER BY o.ordered_at
LIMIT 100;

-- Sản phẩm có nhiều hơn 1 giá hiện hành
SELECT product_id, COUNT(*) AS current_cnt
FROM product_prices
WHERE is_current = 1
GROUP BY product_id
HAVING COUNT(*) > 1;

-- Sản phẩm không có giá hiện hành nào
SELECT product_id
FROM product_prices
GROUP BY product_id
HAVING SUM(is_current = 1) = 0;

SELECT order_id, order_no, 'status không hợp lệ' AS reason
FROM orders WHERE status NOT IN ('pending', 'paid', 'shipped', 'cancelled')
UNION ALL
SELECT order_id, order_no, 'ordered_at ở tương lai'
FROM orders WHERE ordered_at > NOW()
UNION ALL
SELECT order_id, order_no, 'shipped_at trước ordered_at'
FROM orders WHERE shipped_at < ordered_at
UNION ALL
SELECT order_id, order_no, 'đã giao nhưng thiếu shipped_at'
FROM orders WHERE status = 'shipped' AND shipped_at IS NULL
UNION ALL
SELECT customer_id, email, 'email có khoảng trắng thừa'
FROM customers WHERE email <> TRIM(email)
LIMIT 200;

SELECT o.order_no,
       o.total_amount                              AS stored_total,
       COALESCE(SUM(oi.qty * oi.unit_price), 0)    AS actual_total
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.deleted_at IS NULL
GROUP BY o.order_id, o.order_no, o.total_amount
HAVING stored_total <> actual_total
LIMIT 100;

SELECT o.order_no, o.total_amount, COALESCE(SUM(pm.amount), 0) AS paid
FROM orders o
LEFT JOIN payments pm ON pm.order_id = o.order_id
WHERE o.status IN ('paid', 'shipped')
GROUP BY o.order_id, o.order_no, o.total_amount
HAVING paid <> o.total_amount;

SELECT COUNT(*)                                    AS total_rows,
       COUNT(phone)                                AS non_null,
       COUNT(*) - COUNT(phone)                     AS null_cnt,
       SUM(phone = '')                             AS empty_cnt,
       COUNT(DISTINCT phone)                       AS distinct_cnt,
       ROUND(COUNT(DISTINCT phone) / COUNT(*), 4)  AS selectivity,
       MIN(CHAR_LENGTH(phone))                     AS min_len,
       MAX(CHAR_LENGTH(phone))                     AS max_len,
       MIN(phone)                                  AS min_val,
       MAX(phone)                                  AS max_val
FROM customers;

SELECT status,
       COUNT(*)                                          AS cnt,
       ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 2)  AS pct
FROM orders
GROUP BY status
ORDER BY cnt DESC;

status     cnt      pct
shipped    1030412  62.08
cancelled   512330  30.87
paid         98220   5.92
pending      19019   1.15

-- Số đơn và doanh thu theo tháng
SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS month,
       COUNT(*)                         AS orders,
       SUM(total_amount)                AS revenue
FROM orders
WHERE ordered_at >= '2026-01-01'
  AND status <> 'cancelled'
GROUP BY month
ORDER BY month;

WITH RECURSIVE months AS (
  SELECT DATE('2026-01-01') AS m
  UNION ALL
  SELECT m + INTERVAL 1 MONTH FROM months WHERE m < '2026-10-01'
)
SELECT DATE_FORMAT(months.m, '%Y-%m') AS month,
       COUNT(o.order_id)              AS orders
FROM months
LEFT JOIN orders o
       ON o.ordered_at >= months.m
      AND o.ordered_at <  months.m + INTERVAL 1 MONTH
GROUP BY months.m
ORDER BY months.m;

SELECT items_per_order, COUNT(*) AS order_cnt
FROM (
  SELECT order_id, COUNT(*) AS items_per_order
  FROM order_items
  GROUP BY order_id
) t
GROUP BY items_per_order
ORDER BY items_per_order;

items_per_order  order_cnt
1                52310
2                21880
3                 9410
...
350                  2   ← đơn bất thường, cần xem

SELECT
  MIN(CASE WHEN pr >= 0.50 THEN total_amount END) AS p50,
  MIN(CASE WHEN pr >= 0.95 THEN total_amount END) AS p95,
  MIN(CASE WHEN pr >= 0.99 THEN total_amount END) AS p99,
  MAX(total_amount)                               AS max_amount,
  ROUND(AVG(total_amount), 2)                     AS avg_amount
FROM (
  SELECT total_amount, CUME_DIST() OVER (ORDER BY total_amount) AS pr
  FROM orders
  WHERE ordered_at >= '2026-01-01' AND status <> 'cancelled'
) t;

WITH ranked AS (
  SELECT customer_id, order_no, total_amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS rn
  FROM orders
  WHERE deleted_at IS NULL
)
SELECT customer_id, order_no, total_amount
FROM ranked
WHERE rn <= 3
ORDER BY customer_id, rn;

SELECT prev_no + 1 AS gap_start,
       cur_no - 1  AS gap_end,
       cur_no - prev_no - 1 AS missing_cnt
FROM (
  SELECT CAST(order_no AS UNSIGNED) AS cur_no,
         LAG(CAST(order_no AS UNSIGNED)) OVER (ORDER BY CAST(order_no AS UNSIGNED)) AS prev_no
  FROM orders
) t
WHERE cur_no - prev_no > 1
ORDER BY gap_start
LIMIT 100;

SELECT 'legacy' AS src, COUNT(*) AS cnt FROM legacy_db.orders WHERE deleted_at IS NULL
UNION ALL
SELECT 'new',           COUNT(*)        FROM app_db.orders    WHERE deleted_at IS NULL;

SELECT a.month,
       a.cnt AS legacy_cnt,
       b.cnt AS new_cnt,
       COALESCE(b.cnt, 0) - a.cnt AS diff
FROM (SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS month, COUNT(*) AS cnt
      FROM legacy_db.orders GROUP BY month) a
LEFT JOIN (SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS month, COUNT(*) AS cnt
           FROM app_db.orders GROUP BY month) b
       ON b.month = a.month
WHERE NOT (a.cnt <=> b.cnt)
ORDER BY a.month;

WITH a AS (
  SELECT order_no,
         MD5(CONCAT_WS('|', IFNULL(customer_id, '\\N'), IFNULL(status, '\\N'),
                            IFNULL(total_amount, '\\N'), IFNULL(ordered_at, '\\N'))) AS h
  FROM legacy_db.orders
),
b AS (
  SELECT order_no,
         MD5(CONCAT_WS('|', IFNULL(customer_id, '\\N'), IFNULL(status, '\\N'),
                            IFNULL(total_amount, '\\N'), IFNULL(ordered_at, '\\N'))) AS h
  FROM app_db.orders
)
SELECT a.order_no, 'khác giá trị' AS diff_type
FROM a JOIN b USING (order_no)
WHERE a.h <> b.h
UNION ALL
SELECT a.order_no, 'chỉ có ở legacy'
FROM a LEFT JOIN b USING (order_no)
WHERE b.order_no IS NULL
UNION ALL
SELECT b.order_no, 'chỉ có ở new'
FROM b LEFT JOIN a USING (order_no)
WHERE a.order_no IS NULL
LIMIT 500;

SELECT a.order_no,
       a.status        AS legacy_status,  b.status        AS new_status,
       a.total_amount  AS legacy_total,   b.total_amount  AS new_total
FROM legacy_db.orders a
JOIN app_db.orders b USING (order_no)
WHERE NOT (a.status <=> b.status)
   OR NOT (a.total_amount <=> b.total_amount)
LIMIT 100;

-- Dòng có ở legacy nhưng không có (hoặc khác) ở new
SELECT order_no, customer_id, status, total_amount, ordered_at FROM legacy_db.orders
EXCEPT
SELECT order_no, customer_id, status, total_amount, ordered_at FROM app_db.orders;

-- Chạy trên cả hai DB, rồi so sánh kết quả
SELECT FLOOR(order_id / 100000)                                         AS bucket,
       COUNT(*)                                                         AS cnt,
       SUM(CRC32(CONCAT_WS('|', order_no, IFNULL(status, '\\N'),
                                IFNULL(total_amount, '\\N'))))           AS chk
FROM orders
GROUP BY bucket
ORDER BY bucket;

CHECKSUM TABLE app_dev.orders, app_stg.orders;

-- Lưu kết quả của query cũ và query mới vào hai bảng nháp
CREATE TABLE tmp_r_old AS
  SELECT o.order_id FROM /* ... query cũ ... */;

CREATE TABLE tmp_r_new AS
  SELECT o.order_id FROM /* ... query mới ... */;

-- Assertion: số dòng bằng nhau và không có id nào lệch
SELECT (SELECT COUNT(*) FROM tmp_r_old) AS old_cnt,
       (SELECT COUNT(*) FROM tmp_r_new) AS new_cnt,
       (SELECT COUNT(*) FROM tmp_r_old r1
         WHERE NOT EXISTS (SELECT 1 FROM tmp_r_new r2 WHERE r2.order_id = r1.order_id)) AS only_old,
       (SELECT COUNT(*) FROM tmp_r_new r2
         WHERE NOT EXISTS (SELECT 1 FROM tmp_r_old r1 WHERE r1.order_id = r2.order_id)) AS only_new;

DROP TABLE tmp_r_old, tmp_r_new;

SELECT o.customer_id,
       COUNT(DISTINCT o.order_id)                       AS order_cnt,
       COUNT(DISTINCT CASE WHEN o.status = 'cancelled'
                           THEN o.order_id END)         AS cancelled_cnt,
       MAX(ic.item_cnt)                                 AS max_items
FROM orders o
JOIN (SELECT order_id, COUNT(*) AS item_cnt
      FROM order_items GROUP BY order_id) ic
  ON ic.order_id = o.order_id
WHERE o.deleted_at IS NULL
GROUP BY o.customer_id
HAVING order_cnt >= 3
   AND cancelled_cnt > 0
   AND max_items > 5
ORDER BY order_cnt          -- ít đơn nhất trước: dễ kiểm tra bằng mắt
LIMIT 10;

-- Đơn có nhiều sản phẩm nhất (test hiệu năng, phân trang)
SELECT order_id, COUNT(*) AS item_cnt
FROM order_items
GROUP BY order_id ORDER BY item_cnt DESC LIMIT 5;

-- Tên sản phẩm dài nhất (test layout, cắt chữ)
SELECT product_id, name, CHAR_LENGTH(name) AS len
FROM products
ORDER BY len DESC LIMIT 5;

-- Dữ liệu có ký tự đặc biệt (test escape, CSV, encoding)
SELECT product_id, name
FROM products
WHERE name LIKE '%,%' OR name LIKE '%"%' OR name LIKE '%\n%'
   OR name REGEXP '[①-⑳㈱]'
   OR name <> CONVERT(CONVERT(name USING ascii) USING utf8mb4)   -- có ký tự ngoài ASCII
LIMIT 10;

-- 1. Xem trước những dòng sẽ bị ảnh hưởng (cùng WHERE với UPDATE)
SELECT COUNT(*) FROM orders WHERE status = 'paid' AND shipped_at IS NOT NULL;

-- 2. Backup đúng những dòng đó
CREATE TABLE bk_orders_20261104 AS
SELECT * FROM orders WHERE status = 'paid' AND shipped_at IS NOT NULL;

-- 3. Chạy UPDATE trong transaction, kiểm tra số dòng trước khi COMMIT
START TRANSACTION;
UPDATE orders SET status = 'shipped'
WHERE status = 'paid' AND shipped_at IS NOT NULL;
SELECT ROW_COUNT();          -- phải bằng con số ở bước 1
-- COMMIT;  hoặc  ROLLBACK;

-- 4. Verify sau khi COMMIT: so với bản backup
SELECT b.order_id, b.status AS before_status, o.status AS after_status
FROM bk_orders_20261104 b
JOIN orders o USING (order_id)
WHERE NOT (b.status <=> 'paid' AND o.status <=> 'shipped');
-- Mong đợi: 0 dòng