iT邦幫忙

0

[鷹問]SQL問題

小弟遇到一個問題,
我有個頁面需要計算訂單筆數,依照帳號去看日,周,月,年.

畫面大約顯示
帳號 日 週 月 年
從資料庫撈出 帳號今日筆數 帳號這週筆數 帳號今月筆數 帳號今年筆數

不過帳號有51個
51*4 = 204
所以一個畫面要撈204次

有什麼方法可以比較簡單.

我的方法土法煉鋼
寫4個 function
去跑 日 週 月 年
是否有比較好的方法

看更多先前的討論...收起先前的討論...
不曉得你們公司有沒有 每日過帳 機制?
如果有將每日資料匯入 每日業務訂單彙整檔
YY MM YYMM Week Sales_ID Orders

不然就要使用 Pivot
PIVOT語法剖析:

PIVOT的語法分三層,用三個步驟來使用。
第一步驟:先把要PIVOT的原始資料查詢(Query)好。
第二步驟:設定好PIVOT的欄位與方式。
第三步驟:依PIVOT好了的資料,呈現結果。

SELECT <non-pivoted column>, ---- 第三步驟在此,呈現PIVOT後的資料。
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>) ---- 第一步驟在此,準備資料(Query)。
AS <alias for the source query>
PIVOT ---- 第二步驟在此,依第一步驟的資料欄位來設定PIVOT方式。
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

請參考下列網頁:
http://rely1020.blog.ithome.com.tw/post/1606/39111
這個我研究一下,希望不要用到我的function不然感覺撈的資料次數非常多~~!汗
總裁 iT邦好手 1 級 ‧ 2013-05-03 17:15:47 檢舉
chingfeng提到:
撈的資料次數非常多

這樣才有時間休息....睡覺
不能只撈日的資料,其他的用FUNCTION去算嗎??...疑惑
不知道你的資料庫架構

不然先將每日資料匯入 temp talbe
然後在 select 所有 data
再用 變數array 加總。
chingfeng提到:
望不要用到我的function不然感覺撈的資料次數非常


chingfeng您好

小弟這有一個想法,是否能提供您一些思考方向。
第一個沿用您的function
但是在每天固定的一個時間點,做一次全查詢。
查詢完之後,建立一個屬於"要經常調閱的畫面-非即時每日或每小時更新"的view或table
這樣降低系統的負擔。
是否有達到您的需求?
將view或table接到你的查詢畫面..
chingfeng提到:
51*4 = 204
所以一個畫面要撈204次


如果這樣可不可行
只要SELECT 4 次
疑惑

每日:SELECT ACNT, COUNT(ACNT) FROM TABLE WHERE PODATE IS TODAY GROUP BY ACNT
每週:SELECT ACNT, COUNT(ACNT) FROM TABLE WHERE PODATE IN THIS WEEK GROUP BY ACNT
每月:SELECT ACNT, COUNT(ACNT) FROM TABLE WHERE PODATE IN THIS MONTH GROUP BY ACNT
每年:SELECT ACNT, COUNT(ACNT) FROM TABLE WHERE PODATE IN THIS YEAR GROUP BY ACNT
我有一個欄位是紀錄時間的 格式 date("Y-m-d"), 這個思路不錯 一次撈完,就固定在一個暫存的資料表,會再試試看,謝謝指導~~!
就變成大鵰兄的四個function的做法了
不過這個在於沒做INDEX的話是很好辦
其實直接使用黑膜大的做一個function
輸出採取陣列方式輸出最為恰當
這樣的話可以減少多餘程式編寫

2 個回答

8
wiseguy
iT邦超人 1 級 ‧ 2013-05-04 07:47:00
最佳解答

MySQL 的單一 query 解法:

&lt;pre class="c" name="code">select 帳號,
sum(if (日期=CURDATE(),1,0)) as 日,
sum(if (WEEK(日期)=WEEK(CURDATE()),1,0)) as 週,
sum(if (MONTH(日期)=MONTH(CURDATE()),1,0)) as 月,
count(帳號) as 年
from TABLE
group by 帳號

記得把帳號日期設一個索引:
ALTER TABLE 資料表名稱 ADD INDEX (帳號, 日期);

看更多先前的回應...收起先前的回應...
Albert iT邦高手 1 級 ‧ 2013-05-04 12:32:05 檢舉

Albert iT邦高手 1 級 ‧ 2013-05-04 12:35:09 檢舉

只是 [沒有交易資料] 的就進不來了 ?

wiseguy iT邦超人 1 級 ‧ 2013-05-04 18:12:51 檢舉

albertachen提到:
只是 [沒有交易資料] 的就進不來了 ?

謝謝阿伯大指教。
資料不全的問題,當然就只有資料不全的答案。
如果樓主不懂 join 的話,他會再發問的不是嗎?

Albert iT邦高手 1 級 ‧ 2013-05-05 10:32:39 檢舉

Albert iT邦高手 1 級 ‧ 2013-05-05 16:22:06 檢舉

wise guy 這一招真利害

SELECT b.Value,b.Name,
SUM( CASE WHEN DATE_TRUNC('DAY', o.DateOrdered) =DATE_TRUNC('DAY', NOW()) THEN 1 ELSE 0 END) AS 本日,
SUM( CASE WHEN DATE_TRUNC('WEEK', o.DateOrdered) =DATE_TRUNC('WEEK',NOW()) THEN 1 ELSE 0 END) AS 本周,
SUM( CASE WHEN DATE_TRUNC('MONTH',o.DateOrdered) =DATE_TRUNC('MONTH',NOW()) THEN 1 ELSE 0 END) AS 本月,
SUM( CASE WHEN DATE_TRUNC('YEAR', o.DateOrdered) =DATE_TRUNC('YEAR',NOW()) THEN 1 ELSE 0 END) AS 本年
FROM C_BPartner b
LEFT OUTER JOIN C_Order o ON b.C_BPartner_ID=o.C_BPartner_ID
GROUP BY b.Value,b.Name

讚灑花

感謝 wiseguy大大指導,
這個可以使用,繼續研究一下SQL(比較弱),
用CodeIgniter開發習慣了,SQL太少使用了
回不去了哭

12
Albert
iT邦高手 1 級 ‧ 2013-05-03 19:06:34

SELECT Value,Name,
(SELECT COUNT(*) FROM C_Order o WHERE o.C_BPartner_ID=b.C_BPartner_ID AND DATE_TRUNC('DAY',o.DateOrdered)=DATE_TRUNC('DAY',NOW())) AS 本日,
(SELECT COUNT(*) FROM C_Order o WHERE o.C_BPartner_ID=b.C_BPartner_ID AND DATE_TRUNC('WEEK',o.DateOrdered)=DATE_TRUNC('WEEK',NOW())) AS 本周,
(SELECT COUNT(*) FROM C_Order o WHERE o.C_BPartner_ID=b.C_BPartner_ID AND DATE_TRUNC('MONTH',o.DateOrdered)=DATE_TRUNC('MONTH',NOW())) AS 本月,
(SELECT COUNT(*) FROM C_Order o WHERE o.C_BPartner_ID=b.C_BPartner_ID AND DATE_TRUNC('YEAR',o.DateOrdered)=DATE_TRUNC('YEAR',NOW())) AS 本年
FROM C_BPartner b

看更多先前的回應...收起先前的回應...
Albert iT邦高手 1 級 ‧ 2013-05-03 22:46:01 檢舉

很多人
公司都不是給很好訓練
就土法煉鋼
因此
技術還是需要技術轉移顧問

ted99tw iT邦高手 1 級 ‧ 2013-05-03 23:00:50 檢舉

金係太猛了,阿伯大一定也時常在週末衝頂喔..喔..喔...生日快樂

Albert iT邦高手 1 級 ‧ 2013-05-03 23:23:08 檢舉

感謝
泰大 蒞臨指導

謝謝阿伯大,我禮拜一在研究,非上班時間就是休閒~~!驚毆飛

wiseguy iT邦超人 1 級 ‧ 2013-05-04 07:39:28 檢舉

阿伯大的 SQL 明明就藏了五個 query 在裡頭 ...汗

Albert iT邦高手 1 級 ‧ 2013-05-04 12:30:57 檢舉

這是開放原碼

常[讀]開放源碼 ERP

讓你站在巨人肩膀 [看更遠] 防止商業軟體版權控告

Albert iT邦高手 1 級 ‧ 2013-05-04 13:02:13 檢舉

chingfeng提到:
謝謝阿伯大,我禮拜一在研究,非上班時間就是休閒~

我們是東京墨爾本上班時間就開始上班
我們是美西美東都已下班才能下班
....
現在
美東客戶才下班
美西客戶還有人在上班
你太好命了

賽門 iT邦超人 1 級 ‧ 2013-05-04 13:54:32 檢舉

albertachen提到:
我們是東京墨爾本上班時間就開始上班
我們是美西美東都已下班才能下班
....
現在
美東客戶才下班
美西客戶還有人在上班
你太好命了

阿伯大是在7-11上班?? 提供7*24 online service? 這種SLA還真的只有台灣敢承諾!

ted99tw iT邦高手 1 級 ‧ 2013-05-04 18:31:13 檢舉

單一星球7x24是小兒科,只要正三角形頂點有分身即可,阿不然共和國怎麼開星際會議...偷笑

albertachen提到:
你太好命了

一開始條件談好就好了,對於我來說工作是上班時間的事情,下班就是要享受生活!!臉紅
而且下班有時候也有些小案子要做忙

Albert iT邦高手 1 級 ‧ 2013-05-06 09:52:08 檢舉

chingfeng提到:
小案子要做

所以 下班切換 [專案]

因該不算專案
小案子 : 網站 網頁 電腦重灌之類的東西

ted99tw iT邦高手 1 級 ‧ 2013-05-06 10:16:52 檢舉

albertachen提到:
下班切換 [專案]

其實下班後的專案也有分正常與半夜那種的...臉紅

泰大不要太累了 小心肝阿偷笑

ted99tw iT邦高手 1 級 ‧ 2013-05-06 10:27:52 檢舉

半夜的專案通常是比較靠腰的...臉紅

半夜 傷肝 傷腰 ......
有這種情況 請打080009.....驚毆飛

Albert iT邦高手 1 級 ‧ 2013-05-07 15:51:41 檢舉

其實 wiseguy
這招
有一個地方很棒
就是會執行很久很久
如果有一千萬筆
會有機會休息很久
因為在不是用 WHERE 發揮 INDEX
對這種非專業的 chingfeng
也不是要交貨給 IBM 的應該都夠用了

Albert iT邦高手 1 級 ‧ 2013-05-07 15:56:40 檢舉

一般來說我們在資料#計分析
將 :: 年份/年月份/周別/日期 都會設 INDEX
否則你過不了 IBM 驗收
3秒 沒出來就試作資格都沒了

謝謝 阿伯大 指導
這是老闆交代的,資料庫非我設計,而且我也不能動,
只是用既有的資料庫拉資料,而且不常用,我只是先寫個頁面測試,
各邏輯還有流程,到時候會交給委外廠商處理,

工作內容一直變動搖頭

我要發表回答

立即登入回答