用於創建基於多個關係表的報表的 SQL 查詢

我有一個電子商務網站，並且正在創建銷售報告。這些報告需要按年和月劃分。

網站上的每次銷售都會在多個表之間生成一個關係連結，我需要在其中獲取一些值來顯示正確的報告。除了顯示出售了哪些商品外，我還需要顯示，例如，這些銷售中有多少是使用信用卡、金錢等進行的……其中有多少是通過郵局、第三方服務等運送的。

在我繼續之前，請記住我不是數據庫/後端的高級使用者，因此非常歡迎任何改進/建議。

我在後端使用PHP/和一個帶有模型的數據庫，如下圖所示：SQL``MySql

我需要知道的基本資訊是（所有這些都是每個月的總數）：

• 價值：銷售總額；
• 銷售額：總銷售額（基於 orderStatus）；
• 付款：使用每種付款類型的總銷售額；
• 傳遞：使用每種傳遞類型的總銷售額；
• Reported sales：使用者報告的總銷售額（基於 orderStatus）；
• Cancelled sales：取消的總銷售額（基於 orderStatus）；

我能夠建立這個報告，但它似乎很差和/或不是正確的方法來做到這一點。感覺很混亂，所以這就是為什麼我在這裡尋求您的幫助，以指導我如何建構這種類型的查詢並改進我的程式碼。

---

要按年/月創建細分，我使用以下程式碼：

SELECT DISTINCT YEAR(created) as 'year' FROM order
for ($i=1; $i<=12; $i++) { ... }

這是我在其中執行for以獲取每個月的銷售額的程式碼：

//Range between 3-6 are status of OK (shipped, delivered, taken)
$query = "
   FROM order a, orderStatus b
   WHERE YEAR(a.created) = '$year' AND MONTH(a.created) = '$i' 
   AND b.id = a.id_status AND b.status >= 3 AND b.status <= 6
";

$totalAmount = sql("SELECT SUM(a.totalValue) as 'totalValue' $query"); //Total amount in $
$totalSales  = sql("SELECT COUNT(a.id) as 'totalSales' $query");

//Total by delivery type
$totalDelivery = sql("SELECT COUNT(a.id_delivery) as 'totalDelivery' $query AND a.id_delivery = 1");
$totalTaken    = sql("SELECT COUNT(a.id_delivery) as 'totalTaken' $query AND a.id_delivery = 2");

//Total by payment type
$totalOnline = sql("SELECT COUNT(a.id_payment) as 'totalOnline' $query AND a.id_payment = 1");
$totalCard   = sql("SELECT COUNT(a.id_payment) as 'totalCard' $query AND a.id_payment = 2");
$totalMoney  = sql("SELECT COUNT(a.id_payment) as 'totalMoney' $query AND a.id_payment = 3");

//Total canceled/Reported sales
$totalCanceled = sql("SELECT COUNT(a.id) as 'totalCanceled' FROM order a, orderStatus b WHERE YEAR(a.created) = '$year' AND MONTH(a.created) = '$i' AND b.id = a.id_status AND b.status == 7");
$totalCanceled = sql("SELECT COUNT(a.id) as 'totalCanceled' FROM order a, orderStatus b WHERE YEAR(a.created) = '$year' AND MONTH(a.created) = '$i' AND b.id = a.id_status AND b.status == 8");

---

如您所見，sql工作正常，但不是非常理想。此外，感覺就像我在重複自己很多。

例如，我使用每個查詢中最常用的程式碼創建了一個變數，其中大多數需要匹配特定範圍的orderStatus，並且我需要獲取這些資訊的總數。除了canceled和reported銷售，它們是不同的orderStatus。除此之外，我正在對傳遞和付款狀態進行手動檢查，它可能會以某種方式自動化。

我正在做的方式也會返回每種類型的結果，例如，如果沒有用錢付款，它將執行查詢並返回 0。這不是必需的，因為我可以檢查有效和現有的資訊在前端。如果能做到這一點，那就太好了，但如果做不到，只有在有數據儲存的情況下才能得到不是什麼大問題。

---

那麼，如何優化此程式碼，甚至我的數據庫結構，以獲得更好的結果/查詢來生成這些銷售報告？

這可以寫為單個查詢。我猜到了最後一行。

SELECT
  MONTH(a.Created) AS `Month`
, SUM(a.totalValue) as totalValue  -- Total amount in $
, COUNT(a.id) as totalSales
, SUM(CASE WHEN a.id_delivery = 1 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalDelivery
, SUM(CASE WHEN a.id_delivery = 2 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalTaken
, SUM(CASE WHEN a.id_payment = 1 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalTaken
, SUM(CASE WHEN a.id_payment = 2 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalCard
, SUM(CASE WHEN a.id_payment = 3 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalMoney
, SUM(CASE WHEN b.status IN (7, 8) THEN 1 END) as totalCanceled
FROM order a, orderStatus b
WHERE YEAR(a.Created) = ?
AND b.id = a.id_status
GROUP BY MONTH(a.Created)
ORDER BY 1

這很長，但是您提到您是初學者。幾點觀察：

• 不要將數字屬性儲存為 VARCHAR。SUM 函式甚至可以處理字元串嗎？
• 屬性查找表很方便，但我建議使用字元串作為標記而不是數字。程式碼範例中的註釋說明了一切——誰會知道 3 是可以的。
• 請閱讀綁定變數
• 你將如何更新使用者的年齡？

您應該始終為每一列使用適當的數據類型。即時進行數據轉換是一種會產生糟糕的性能和數據質量問題的方法。

我使用屬性查找表，甚至幾年前使用過您的方法——CPU 性能和記憶體非常寶貴。這真的不是問題，在您的數據庫上編寫查詢的人討厭在查找表上進行聯接。如果數字程式碼不進行連接，則編碼數字程式碼是錯誤的來源。只需使用字元串標記作為鍵。

您幾乎不應該將年齡儲存在包含有關人員屬性的表的列中——它是基於目前日期和他們的出生日期的派生值。更根本的是，為什麼你甚至需要他們的年齡？如果一個人不提供他們的出生日期或年齡，他們就不能下訂單？我提出這個問題是因為您應該能夠證明為什麼需要每條數據，尤其是與安全和隱私相關的屬性。如果您的應用程序必須接受資訊安全審計，您應該能夠保護您儲存的內容以及您如何保護它。如果您的公司/客戶違反法定要求（如 GDPR）或政府/行業法規，您是否要承擔責任？請注意您在數據庫中儲存的內容！

如果不使用綁定變數，數據庫將不得不解析和優化每個查詢，並且可能不會重用記憶體結果，從而導致性能下降。

除此之外，如果您在使用者輸入中使用字元串連接，那麼讓我將您介紹給我的朋友 Little Bobby Tables ( https://xkcd.com/327/ )。如果您不對使用者輸入使用綁定變數，您的應用程序將不會通過安全審核。雖然這不是此程式碼片段的問題，但我想提出這一點，以防您不知道。

引用自：https://dba.stackexchange.com/questions/154761