我們在"WordPress 加上 Woocommerce 電子商務，如何從後台撈出商品資料?"，已經知道如何從後台撈出商品資料，現在我們來看看如何從後台撈出[訂單資料]。
首先要確定你的 Woocommerce 是舊的還是 HPOS (High Performance Order Storage 高效能訂單儲存) 的結構。
HPOS 高效能訂單儲存是什麼? 它是 WooCommerce 把「訂單」從 WordPress 的 posts/postmeta 架構搬出去，改用專用訂單資料表來儲存的一套機制（以前叫做 Custom Order Tables）。它採用 WooCommerce 的 CRUD（Create, Read, Update, Delete） 設計，讓訂單查詢更符合電商需求、也更好擴充與最佳化效能。
以往 WooCommerce 會把訂單當成一種自訂文章類型，存在 _posts、細節存在 _postmeta。當訂單量逐漸增大時，postmeta 會變成超肥的大表，查詢與寫入都容易變慢、也會拖累整個網站（因為很多外掛與功能也在用同一套 posts 架構）。HPOS 透過「拆成訂單專用表＋專用索引（index）」來減少讀寫負擔、降低擁塞，提升可擴充性與穩定性。
使用以下指令來確認是否為 HPOS :
SELECT option_name, option_value
FROM wpdp_options
WHERE option_name IN (
'woocommerce_custom_orders_table_enabled',
'woocommerce_custom_orders_table_data_sync_enabled'
);
以上的 wpdp_options 需要依照你的環境來修改，可能是 wp_options ，也可能是其他前置詞。
如果沒發生錯誤，可以看到以下結果 :
舊式的還是 HPOS 有何差別呢 ?
舊式 ：把「訂單」當成 WordPress 的自訂文章類型，主要落在 _posts、_postmeta。
在 _posts 中會看到 post_type = 'shop_order' 的資料就是訂單。訂單很多欄位(例如 Email、地址、付款方式、金額等)分散在 _postmeta，所以常見查詢會需要把 posts + postmeta JOIN 好幾次。
HPOS (High-Performance Order Storage)：把「訂單」改存到 WooCommerce 的專用訂單資料表(例如 _wc_orders 等)，用更適合電商查詢的欄位與索引，減少對 postmeta 的依賴。把原本散在 _posts、_postmeta 的「訂單常用欄位」搬到真正的欄位，並拆成 4 張核心表 : _wc_orders、_wc_order_addresses、_wc_order_operational_data、_wc_orders_meta
如果使用 HPOS 並相容模式開啟，訂單資料就會放在[舊式+HPOS]的表單，新舊會維持同步；如果使用 HPOS 並相容模式關閉，訂單主要資料就會放在 HPOS 的表單，而 _posts 則「不再放完整訂單資料」。
在 WordPress 後台看到的訂單資訊，如下圖 :
id：order_id 訂單編號
status：訂單狀態，例如 wc-pending、wc-processing、wc-completed…
currency：錢幣單位
type：shop_order / shop_order_refund（退款會用到）
tax_amount： 稅額
total_amount： 總額
customer_id：對應 _users.ID (訪客可能是 0)
billing_email： 帳單地址
date_created_gmt、date_updated_gmt：建立更新日期
parent_order_id：退款指向原始訂單
payment_method、payment_method_title： 付款方式
ip_address、user_agent： IP地址 瀏覽器類型
customer_note：客戶備註
可以從資料表 _wc_orders 列出資料，如下圖 :
可以從資料表 _wc_order_addresses：帳單/收貨地址 列出資料，如下圖 :
可以從資料表 _wc_order_operational_data：內部狀態/旗標/部分金額 列出資料，如下圖 :
可以從資料表 _wc_orders_meta：訂單 meta 列出資料，如下圖 :
以下是使用 HPOS 常用撈訂單資料的指令範本 :
(1) 撈訂單列表 (主檔 + 帳單地址 + operational)
SELECT
o.id,
o.date_created_gmt,
o.date_updated_gmt,
o.status,
o.type,
o.currency,
o.total_amount,
o.tax_amount,
o.payment_method,
o.payment_method_title,
o.billing_email,
op.created_via,
op.date_paid_gmt,
op.date_completed_gmt,
op.shipping_total_amount,
op.shipping_tax_amount,
op.discount_total_amount,
op.discount_tax_amount,
b.first_name AS billing_first_name,
b.last_name AS billing_last_name,
b.company AS billing_company,
b.phone AS billing_phone,
b.address_1 AS billing_address_1,
b.address_2 AS billing_address_2,
b.city AS billing_city,
b.state AS billing_state,
b.postcode AS billing_postcode,
b.country AS billing_country,
s.first_name AS shipping_first_name,
s.last_name AS shipping_last_name,
s.company AS shipping_company,
s.phone AS shipping_phone,
s.address_1 AS shipping_address_1,
s.address_2 AS shipping_address_2,
s.city AS shipping_city,
s.state AS shipping_state,
s.postcode AS shipping_postcode,
s.country AS shipping_country
FROM wpdp_wc_orders o
LEFT JOIN wpdp_wc_order_operational_data op
ON op.order_id = o.id
LEFT JOIN wpdp_wc_order_addresses b
ON b.order_id = o.id AND b.address_type = 'billing'
LEFT JOIN wpdp_wc_order_addresses s
ON s.order_id = o.id AND s.address_type = 'shipping'
WHERE o.type = 'shop_order'
ORDER BY o.id DESC
LIMIT 100;
撈出訂單列表，如下圖 :
(2) 撈「等待 BACS 付款」的訂單
SELECT
o.id,
o.date_created_gmt,
o.status,
o.total_amount,
o.currency,
o.payment_method,
o.payment_method_title,
o.billing_email
FROM wpdp_wc_orders o
WHERE o.type = 'shop_order'
AND o.payment_method = 'bacs'
AND o.status IN ('wc-on-hold', 'wc-pending')
ORDER BY o.id DESC
LIMIT 200;
撈「等待 BACS 付款」的訂單，如下圖 :
(3) 撈某張訂單的「商品明細」(line_item：商品ID、數量、金額)
SELECT
oi.order_item_id,
oi.order_item_name,
CAST(MAX(CASE WHEN oim.meta_key = '_product_id' THEN oim.meta_value END) AS UNSIGNED) AS product_id,
CAST(MAX(CASE WHEN oim.meta_key = '_variation_id' THEN oim.meta_value END) AS UNSIGNED) AS variation_id,
CAST(MAX(CASE WHEN oim.meta_key = '_qty' THEN oim.meta_value END) AS DECIMAL(18,2)) AS qty,
CAST(MAX(CASE WHEN oim.meta_key = '_line_total' THEN oim.meta_value END) AS DECIMAL(18,2)) AS line_total,
CAST(MAX(CASE WHEN oim.meta_key = '_line_tax' THEN oim.meta_value END) AS DECIMAL(18,2)) AS line_tax
FROM wpdp_woocommerce_order_items oi
JOIN wpdp_woocommerce_order_itemmeta oim
ON oim.order_item_id = oi.order_item_id
WHERE oi.order_id = 77
AND oi.order_item_type = 'line_item'
GROUP BY oi.order_item_id, oi.order_item_name
ORDER BY oi.order_item_id;
撈出特定訂單 (#77) 的「商品明細」，如下圖 :
(4) 撈退款 (refund) 與原訂單關聯
SELECT
r.id AS refund_id,
r.parent_order_id AS order_id,
r.status AS refund_status,
r.currency,
r.total_amount AS refund_amount,
r.date_created_gmt
FROM wpdp_wc_orders r
WHERE r.type = 'shop_order_refund'
AND r.parent_order_id = 12345
ORDER BY r.id DESC;
(5) 撈訂單 meta (外掛/金流自訂資料)
SELECT
m.id,
m.meta_key,
m.meta_value
FROM wpdp_wc_orders_meta m
WHERE m.order_id = 77
ORDER BY m.id;
撈訂單 meta ，如下圖 :
(6) 撈訂單備註 (Order Notes)
SELECT
c.comment_ID,
c.comment_date_gmt,
c.comment_author,
c.comment_content
FROM wpdp_comments c
WHERE c.comment_post_ID = 12345
AND c.comment_type = 'order_note'
ORDER BY c.comment_ID DESC;
撈訂單備註 ，如下圖 :
以下是使用 HPOS 常用報表的指令範本 :
先設定報表期間
-- 近 30 天 (UTC)
SET @start := DATE_SUB(UTC_DATE(), INTERVAL 30 DAY);
SET @end := DATE_ADD(UTC_DATE(), INTERVAL 1 DAY);
通常會計入：wc-processing, wc-completed
通常不計入：wc-pending, wc-on-hold, wc-cancelled, wc-failed, wc-refunded
(1) 訂單清單 (老闆最常要：最近 100 筆含金額、付款、客戶、地址)
SELECT
o.id,
o.date_created_gmt,
o.status,
o.currency,
o.total_amount,
o.tax_amount,
o.payment_method,
o.payment_method_title,
o.billing_email,
b.first_name AS billing_first_name,
b.last_name AS billing_last_name,
b.phone AS billing_phone,
s.city AS shipping_city,
s.postcode AS shipping_postcode
FROM wpdp_wc_orders o
LEFT JOIN wpdp_wc_order_addresses b
ON b.order_id = o.id AND b.address_type = 'billing'
LEFT JOIN wpdp_wc_order_addresses s
ON s.order_id = o.id AND s.address_type = 'shipping'
WHERE o.type = 'shop_order'
ORDER BY o.id DESC
LIMIT 100;
(2) 每日訂單數、每日營收、客單價 AOV (近 30 天)
SELECT
DATE(o.date_created_gmt) AS day_utc,
COUNT(*) AS order_count,
SUM(o.total_amount) AS revenue,
AVG(o.total_amount) AS aov
FROM wpdp_wc_orders o
WHERE o.type = 'shop_order'
AND o.status IN ('wc-processing','wc-completed')
AND o.date_created_gmt >= @start AND o.date_created_gmt < @end
GROUP BY DATE(o.date_created_gmt)
ORDER BY day_utc DESC;
(3) 每月訂單數、每月營收 (近 12 個月)
SELECT
DATE_FORMAT(o.date_created_gmt, '%Y-%m') AS ym,
COUNT(*) AS order_count,
SUM(o.total_amount) AS revenue,
AVG(o.total_amount) AS aov
FROM wpdp_wc_orders o
WHERE o.type = 'shop_order'
AND o.status IN ('wc-processing','wc-completed')
AND o.date_created_gmt >= DATE_SUB(UTC_DATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(o.date_created_gmt, '%Y-%m')
ORDER BY ym DESC;
(4) 訂單狀態分布 (近 30 天)
SELECT
o.status,
COUNT(*) AS cnt,
SUM(o.total_amount) AS total_amount
FROM wpdp_wc_orders o
WHERE o.type = 'shop_order'
AND o.date_created_gmt >= @start AND o.date_created_gmt < @end
GROUP BY o.status
ORDER BY cnt DESC;
(5) 付款方式分布 (近 30 天，含 BACS)
SELECT
o.payment_method,
o.payment_method_title,
COUNT(*) AS cnt,
SUM(o.total_amount) AS revenue
FROM wpdp_wc_orders o
WHERE o.type = 'shop_order'
AND o.date_created_gmt >= @start AND o.date_created_gmt < @end
GROUP BY o.payment_method, o.payment_method_title
ORDER BY revenue DESC;
(6)「等待 BACS 付款」清單 (對帳用)
SELECT
o.id,
o.date_created_gmt,
o.status,
o.total_amount,
o.billing_email
FROM wpdp_wc_orders o
WHERE o.type = 'shop_order'
AND o.payment_method = 'bacs'
AND o.status IN ('wc-on-hold','wc-pending')
ORDER BY o.id DESC;
(7) 客戶貢獻度 TOP 20 (會員 + 訪客用 Email 合併)
SELECT
CASE
WHEN o.customer_id IS NULL OR o.customer_id = 0 THEN CONCAT('guest:', o.billing_email)
ELSE CONCAT('user:', o.customer_id)
END AS customer_key,
COUNT(*) AS orders,
SUM(o.total_amount) AS revenue,
MAX(o.date_created_gmt) AS last_order_gmt
FROM wpdp_wc_orders o
WHERE o.type = 'shop_order'
AND o.status IN ('wc-processing','wc-completed')
AND o.date_created_gmt >= @start AND o.date_created_gmt < @end
GROUP BY customer_key
ORDER BY revenue DESC
LIMIT 20;
(8) 新客 vs 回購 (近 30 天)
WITH base AS (
SELECT
o.id,
o.date_created_gmt,
o.total_amount,
CASE
WHEN o.customer_id IS NULL OR o.customer_id = 0 THEN CONCAT('guest:', o.billing_email)
ELSE CONCAT('user:', o.customer_id)
END AS customer_key
FROM wpdp_wc_orders o
WHERE o.type = 'shop_order'
AND o.status IN ('wc-processing','wc-completed')
),
first_order AS (
SELECT customer_key, MIN(date_created_gmt) AS first_date_gmt
FROM base
GROUP BY customer_key
)
SELECT
DATE(b.date_created_gmt) AS day_utc,
SUM(CASE WHEN b.date_created_gmt = f.first_date_gmt THEN 1 ELSE 0 END) AS new_customers_orders,
SUM(CASE WHEN b.date_created_gmt > f.first_date_gmt THEN 1 ELSE 0 END) AS returning_orders,
SUM(b.total_amount) AS revenue
FROM base b
JOIN first_order f ON f.customer_key = b.customer_key
WHERE b.date_created_gmt >= @start AND b.date_created_gmt < @end
GROUP BY DATE(b.date_created_gmt)
ORDER BY day_utc DESC;
(9) 熱賣商品 TOP 10 (數量、營收)
WITH line_items AS (
SELECT
oi.order_id,
CAST(MAX(CASE WHEN oim.meta_key = '_product_id' THEN oim.meta_value END) AS UNSIGNED) AS product_id,
CAST(MAX(CASE WHEN oim.meta_key = '_qty' THEN oim.meta_value END) AS DECIMAL(18,2)) AS qty,
CAST(MAX(CASE WHEN oim.meta_key = '_line_total' THEN oim.meta_value END) AS DECIMAL(18,2)) AS line_total
FROM wpdp_woocommerce_order_items oi
JOIN wpdp_woocommerce_order_itemmeta oim
ON oim.order_item_id = oi.order_item_id
WHERE oi.order_item_type = 'line_item'
GROUP BY oi.order_id, oi.order_item_id
)
SELECT
li.product_id,
p.post_title AS product_title,
SUM(li.qty) AS total_qty,
SUM(li.line_total) AS revenue
FROM line_items li
JOIN wpdp_wc_orders o ON o.id = li.order_id
LEFT JOIN wpdp_posts p ON p.ID = li.product_id
WHERE o.type = 'shop_order'
AND o.status IN ('wc-processing','wc-completed')
AND o.date_created_gmt >= @start AND o.date_created_gmt < @end
GROUP BY li.product_id, p.post_title
ORDER BY revenue DESC
LIMIT 10;
(10) 城市別營收 (用收貨地址 shipping city)
SELECT
COALESCE(s.city, '(unknown)') AS shipping_city,
COUNT(*) AS order_count,
SUM(o.total_amount) AS revenue
FROM wpdp_wc_orders o
LEFT JOIN wpdp_wc_order_addresses s
ON s.order_id = o.id AND s.address_type = 'shipping'
WHERE o.type = 'shop_order'
AND o.status IN ('wc-processing','wc-completed')
AND o.date_created_gmt >= @start AND o.date_created_gmt < @end
GROUP BY shipping_city
ORDER BY revenue DESC;
(11) 優惠券使用統計 (coupon code、使用次數、帶來的訂單營收)
SELECT
oi.order_item_name AS coupon_code,
COUNT(DISTINCT oi.order_id) AS used_orders,
SUM(o.total_amount) AS revenue
FROM wpdp_woocommerce_order_items oi
JOIN wpdp_wc_orders o ON o.id = oi.order_id
WHERE oi.order_item_type = 'coupon'
AND o.type = 'shop_order'
AND o.status IN ('wc-processing','wc-completed')
AND o.date_created_gmt >= @start AND o.date_created_gmt < @end
GROUP BY oi.order_item_name
ORDER BY used_orders DESC, revenue DESC;
(12) 運送方式與運費統計 (shipping method、運費總額)
SELECT
oi.order_item_name AS shipping_method,
COUNT(DISTINCT oi.order_id) AS orders,
SUM(CAST(oim.meta_value AS DECIMAL(18,2))) AS shipping_cost
FROM wpdp_woocommerce_order_items oi
JOIN wpdp_woocommerce_order_itemmeta oim
ON oim.order_item_id = oi.order_item_id
JOIN wpdp_wc_orders o
ON o.id = oi.order_id
WHERE oi.order_item_type = 'shipping'
AND oim.meta_key = 'cost'
AND o.type = 'shop_order'
AND o.status IN ('wc-processing','wc-completed')
AND o.date_created_gmt >= @start AND o.date_created_gmt < @end
GROUP BY oi.order_item_name
ORDER BY shipping_cost DESC;
(13) 稅額統計 (商品稅 + 運費稅)
SELECT
DATE(o.date_created_gmt) AS day_utc,
SUM(o.tax_amount) AS items_tax,
SUM(op.shipping_tax_amount) AS shipping_tax,
SUM(o.tax_amount + COALESCE(op.shipping_tax_amount,0)) AS total_tax
FROM wpdp_wc_orders o
LEFT JOIN wpdp_wc_order_operational_data op
ON op.order_id = o.id
WHERE o.type = 'shop_order'
AND o.status IN ('wc-processing','wc-completed')
AND o.date_created_gmt >= @start AND o.date_created_gmt < @end
GROUP BY DATE(o.date_created_gmt)
ORDER BY day_utc DESC;
(14) 退款統計 (refund 數、退款金額)
SELECT
DATE_FORMAT(r.date_created_gmt, '%Y-%m') AS ym,
COUNT(*) AS refund_count,
SUM(r.total_amount) AS refund_amount
FROM wpdp_wc_orders r
WHERE r.type = 'shop_order_refund'
AND r.date_created_gmt >= DATE_SUB(UTC_DATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(r.date_created_gmt, '%Y-%m')
ORDER BY ym DESC;
(15) 指定商品在近 30 天被買了多少、賣了多少 (老闆點名款)
SET @pid := 123;
WITH line_items AS (
SELECT
oi.order_id,
CAST(MAX(CASE WHEN oim.meta_key = '_product_id' THEN oim.meta_value END) AS UNSIGNED) AS product_id,
CAST(MAX(CASE WHEN oim.meta_key = '_qty' THEN oim.meta_value END) AS DECIMAL(18,2)) AS qty,
CAST(MAX(CASE WHEN oim.meta_key = '_line_total' THEN oim.meta_value END) AS DECIMAL(18,2)) AS line_total
FROM wpdp_woocommerce_order_items oi
JOIN wpdp_woocommerce_order_itemmeta oim
ON oim.order_item_id = oi.order_item_id
WHERE oi.order_item_type = 'line_item'
GROUP BY oi.order_id, oi.order_item_id
)
SELECT
SUM(li.qty) AS total_qty,
SUM(li.line_total) AS revenue
FROM line_items li
JOIN wpdp_wc_orders o ON o.id = li.order_id
WHERE li.product_id = @pid
AND o.type = 'shop_order'
AND o.status IN ('wc-processing','wc-completed')
AND o.date_created_gmt >= @start AND o.date_created_gmt < @end;
後續再來詳細說明 : 如何從後台撈出更多的訂單資料。
0 留言