Thu 20 Dec 2007 16:06:01
ก่อนอื่นต้องขอชื่นชมในน้ำใจอันดีของ Admin ที่คอยตอบคำถามแบบให้กระจ่างกันไปข้างหนึ่ง และไม่ใช่แบบงูๆปลาๆ แต่ในระดับที่เรียกว่าหาตัวจับยากคนหนึ่งในวงการโปรแกรมเมอร์น่ะครับ ขอให้อานิสงส์ของการให้ความรู้เป็นทานจงส่งผลให้ Admin และธุรกิจจงไปได้ด้วยดีนะครับ สาธุ
คราวนี้มาถึงปัญหาของผมบ้าง คือผมมีฐานข้อมูลนักเรียนอยู่ชุดหนึ่ง (4000+ แถว) เป็นข้อมูลการใช้งานศูนย์คอมพิวเตอร์ของโรงเรียนน่ะครับ และผมต้องการจะทำรายงานประจำเดือนขึ้นมาเพื่อหาความถี่ของนักเรียนที่เข้าใช้ จำแนกตามสาขา และวัน โดยข้อมูลที่ต้องการมีดังนี้
1.แผนก
2.ชั้นปี
แต่ปัญหาก็ติดอยู่ตรงที่ จำนวนของผู้เข้าใช้บริการมันไม่ยอมแสดงออกมาเป็นวันๆ (แถว) ครับ มันออกมาทั้งเดือนเลย ผมงมอยู่เป็นสัปดาห์แล้ว เปลี่ยนรูปแบบมาหลายครั้งแล้ว ได้ตัวอย่างจากคุณอนันต์มาเป็นแนวทางประยุกต์ใช้ก็มาก แต่ว่าก็ยังไม่ได้ดังใจ ช่วยหน่อยนะครับ
Thu 20 Dec 2007 16:09:01
ขอโทษทีครับ เปลี่ยนลิ้งค์หน่อย
Thu 20 Dec 2007 22:11:43
ผมได้ลองทำการดูตารางแล้ว เขียน Query ได้ประมาณนี้
SELECT u.udate, SUBSTR(i.id, 1, 2) AS uYear, SUBSTR(i.id, 3,4) AS uDept, COUNT(i.no) AS total FROM inet_usage i INNER JOIN (SELECT no, STR_TO_DATE(CONCAT(SUBSTR(s.date, 1, 2 ), '/', SUBSTR(s.date, 4, 2 ), '/', (CAST(SUBSTR(s.date, 7, 2 ) AS UNSIGNED) - 43)), '%d/%m/%y') AS udate FROM inet_usage s) AS u ON u.no = i.no WHERE MONTH(u.udate)=11 AND YEAR(u.udate)=2007 GROUP BY uYear, uDept, u.udate
จะใช้เวลาในการ Query ประมาณ 3.7989 sec นับว่าช้ามากๆ ผมจึงทำการสร้าง Primary key ที่ no และทำการ Index ที่ id, date เพื่อให้ทำการได้เร็วขึ้น ได้ผลความเร็วที่ 0.0967 sec จะเห็นได้ว่าความเร็วต่างกันเกือบ 3 วินาที/ครั้ง ในทีนี้คุณจะสามารถลดการทำงานของ php จาก ~600 บรรทัด เหลือเพียง ~100 บรรทัดเท่านั้น
ที่ทำงานค่อนข้างช้าเนื่องจากผมทำการ Convert field date ของคุณซึ่งเก็บแบบ Varchar และเป็นปี พ.ศ. ให้เป็นชนิด Date ใน MySQL เผื่อการใช้งานได้ง่ายขึ้นถ้าต้องการใช้ในการคำนวน
ส่วนการแสดงผลอื่นๆลองเอาไป GROUP ดูเองนะครับ
ปล. ผมขออนุญาตเอากรณีของคุณเป็นกรณีศึกษาเพื่อเขียนบทความได้หรือไม่ครับ
Fri 21 Dec 2007 08:24:10
ด้วยความยินดีครับ เอาสคริปต์ของผมมาเผยแพร่ก็ได้ (แทนคำขอบคุณ) เผื่อบทความที่เกิดจากปัญหาของผมจะเป็นประโยชน์ในการพัฒนาเว็บไซต์ทางการศึกษาและด้านอื่นๆ
Fri 21 Dec 2007 10:19:45
Fri 21 Dec 2007 10:58:34
ผมได้ทดลองใช้ query แบบนี้ดูแล้วปรากฏว่าได้ผลดีมาก วันเรียงกันออกมาทุกวันในเดือน 11 แต่มีปัญหาอยู่อีกอย่างหนึ่งคือว่า
มันนับจำนวนข้อมูลทั้งหมดที่อยู่ในเงื่อนไข (เช่น 2007-11-01 มี 164 ข้อมูล มันก็จะแสดงทั้งหมดเลย) ถ้าผมจะกรองเอาเฉพาะที่ต้องการ (เช่น 502101% ในวันที่ 2007-11-01 ซึ่งค่าเท่ากับ 13 จากคำสั่ง select count(*) from inet_usage where id like '502101%' and date = '01/11/50';)
คือจากคอนเซ็ปต์เก่าของผม ผมใช้วิธีในการหาค่าความถี่ของแต่ละแผนก-ชั้นปี (502=ปวช.1/503=ปวส.1,482=ปวช.2/483=ปวส.2 ; 101-แผนก1, 102-แผนก2....) เช่น 502101%,492101%,482101%, 503101% และ 493101% ณ วันที่ 2007/11/01 ดังนี้
503101
select count(*) from inet_usage where id like '502101%' and date = $mon ซึ่งผมจะได้ค่าของจำนวนนักเรียนกลุ่มนี้ ณ วันที่ $mon ออกมา ซึ่งผมต้องทำทั้งหมด เท่ากับจำนวนแผนกที่มี แต่ว่าปัญหามันอยู่ที่
ผมจะทำอย่างไรให้มันแจกแจงจำนวนนักเรียนแต่ละแผนก ณ วันหนึ่งๆโดยเฉพาะ แล้วค่อยนับเอาจำนวนทั้งหมดมาเป็น total
กรุณาด้วยนะครับ อยากรู้มากๆ
Fri 21 Dec 2007 14:08:02
ส่วนการแบ่งกลุ่มของนักศึกษานั้น จะมีการใช้งานแบ่ง String ออกมาเช่น SUBSTR(i.id, 1, 2) AS uYear เป็นปีที่เข้าศึกษา ถ้าคุณต้องการก็สามารถประยุกต์ใช้ได้เช่น
SUBSTR(i.id, 1, 3) AS uYear คุณก็จะได้ชั่นปีตามต้องการครับ
SUBSTR(i.id, 4,3) AS uDept คุณก็จะได้แผนก
จะเห็นว่าผมทำการสร้าง Alias ของทุก field ที่มีการแปลงเช่น uYear, uDept เพื่อนำมาใช้ประโยชน์ในการ GROUP BY clause ด้วย
เมื่อนำทั้งหมดมารวมเข้าด้วยกัน SELECT .... FROM .... WHERE u.date=STR_TO_DATE('2007/11/01', '%Y/%m/%d') GROUP BY uYear, uDept จะไม่มี u.udate ใน GROUP BY clause เนื่องจากคุณทำการระบุวันที่ลงไปแล้ว
ประโยชน์อีกอย่างของการแปลงข้อมูล String เป็นชนิด Date คือคุณสามารถใช้ WHERE u.date>=STR_TO_DATE('2007/11/01', '%Y/%m/%d') AND u.date<=STR_TO_DATE('2007/12/10', '%Y/%m/%d') คือการเลือกข้อมูลระหว่างวันที่ที่ระบุได้
ลองเล่นดูครับ การใช้คำสั่งต่างๆไม่จำเป็นต้องใช้ php ถ้ามันมีอยู่ใน MySQL อยู่แล้วก็พยายามใช้คำสั่งใน MySQL จะทำงานได้เร็วกว่า คำสั่งต่างๆก็อ้างอิงได้ตาม http://dev.mysql.com/doc/refman/5.0/en/functions.html
Fri 21 Dec 2007 15:12:50
ผมได้ทดลองใช้ q ดังนี้
<?
include_once('connect.php');
mysql_select_db($dbname);
$sql = "SELECT u.udate, SUBSTR(i.id, 1, 3) AS uYear, SUBSTR(i.id, 4,3) AS uDept, COUNT(i.no) AS total FROM inet_usage i INNER JOIN (SELECT no, STR_TO_DATE(CONCAT(SUBSTR(s.date, 1, 2 ), '/', SUBSTR(s.date, 4, 2 ), '/', (CAST(SUBSTR(s.date, 7, 2 ) AS UNSIGNED) - 43)), '%d/%m/%y') AS udate FROM inet_usage s) AS u ON u.no = i.no WHERE u.date>=STR_TO_DATE('2007/11/01', '%Y/%m/%d') AND u.date<=STR_TO_DATE('2007/12/10', '%Y/%m/%d') GROUP BY uYear, uDept, u.udate ";
$result=mysql_query($sql);
while($record = mysql_fetch_array($result)){
echo $record[udate]." ";
echo "uYear: ".$record[uYear]." ";
echo "uDept: ".$record[uDept]." ";
echo "Total: ".$record[total]."<br />";
}
?>
แล้วปรากฏว่ามันแสดงผลดังนี้
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\AppServ\www\iNet\D101A2.php on line 7
แล้วพอเอาไปรันบน mySql ด้วย
SELECT u.udate, SUBSTR( i.id, 1, 3 ) AS uYear, SUBSTR( i.id, 4, 3 ) AS uDept, COUNT( i.no ) AS total ) AS u ON u.no = i.no มันกลับฟ้องว่า... #1054 - Unknown column 'u.date' in 'where clause' ไม่ทราบว่ามันผิดตรงไหน รบกวนดูให้หน่อยครับ
FROM inet_usage i
INNER JOIN (
SELECT no, STR_TO_DATE( CONCAT( SUBSTR( s.date, 1, 2 ) , '/', SUBSTR( s.date, 4, 2 ) , '/', (
FROM inet_usage s
WHERE u.date >= STR_TO_DATE( '2007/11/01', '%Y/%m/%d' )
AND u.date <= STR_TO_DATE( '2007/12/10', '%Y/%m/%d' )
GROUP BY uYear, uDept, u.udate
SELECT no, STR_TO_DATE( CONCAT( SUBSTR( s.date, 1, 2 ) , '/', SUBSTR( s.date, 4, 2 ) , '/', (
FROM inet_usage s
) AS u ON u.no = i.no
WHERE u.date >= STR_TO_DATE( '2007/11/01', '%Y/%m/%d' )
AND u.date <= STR_TO_DATE( '2007/12/10', '%Y/%m/%d' )
GROUP BY uYear, uDept, u.udate
มันกลับฟ้องว่า...
#1054 - Unknown column 'u.date' in 'where clause'
ไม่ทราบว่ามันผิดตรงไหน รบกวนดูให้หน่อยครับ
Fri 21 Dec 2007 16:12:52
AND u.udate <= STR_TO_DATE( '2007/12/10', '%Y/%m/%d' ) ขออภัยครับเขียนตกไปครับ
Sat 22 Dec 2007 16:15:28
| วันที่ |
แผนกที่1 | ||||||||||
| 1 พฤศจิกายน 2550 |
|
การกำหนดเงื่อนไขด้านบนผมทำได้แล้วครับขอบคุณมาก ออกมาได้ผลเป็นที่น่าพอใจ แต่ที่ผมต้องการจริงๆมันอยู่ในรูปแบบนี้ซึ่งผมไม่รู้ว่าจะต้องทำอย่างไรดีจึงจะให้ผลมันจำแนกออกมาเป็นแผนกๆ ในแต่ละวันแบบนี้ ผมลองใช้การ query 1 ชุดต่อ 1 ค่าดูแล้วปรากฎว่ามันช้ามากๆ ก็เลยไม่รู้จะต่ออย่างไร ถ้าเว็บมาสเตอร์ว่างรบกวนทางนี้ด้วยนะครับ ขอบคุณครับ
Sat 22 Dec 2007 17:47:11
จากตัวอย่างการแสดงผลที่ยกตัวอย่างมานั้น ต้องใช้ query ตามจำนวนแผนกที่มีครับ จะเร็วที่สุด
SELECT u.udate, SUBSTR(i.id, 1, 3) AS uYear, COUNT(i.no) AS total FROM inet_usage i INNER JOIN (SELECT no, STR_TO_DATE(CONCAT(SUBSTR(s.date, 1, 2 ), '/', SUBSTR(s.date, 4, 2 ), '/', (CAST(SUBSTR(s.date, 7, 2 ) AS UNSIGNED) - 43)), '%d/%m/%y') AS udate FROM inet_usage s) AS u ON u.no = i.no WHERE u.udate>=STR_TO_DATE('2007/11/01', '%Y/%m/%d') AND u.udate<=STR_TO_DATE('2007/11/30', '%Y/%m/%d') AND SUBSTR(i.id, 4,3)='100' GROUP BY u.udate, uYear
จะเห็นว่าผมระบุเงื่อนไข SUBSTR(i.id, 4,3)='100' เพื่อระบุแผนกที่ต้องการ และก็ตัดการ GROUP BY uDept ออกเนื่องจากเราระบุแผนกลงไปแล้วจึงไม่มีความจำเป็นให้ MySQL ไป Group ให้ทำงานหนักโดยไม่จำเป็นออก
ลองเอาไปประยุกต์ดูครับ ใส่ Condition ที่ต้องการลงไปเพื่อให้ได้ผลที่ต้องการ แล้วจัดรูปแบบอีกที การ query ต้องดูตามรูปแบบของการแสดงผลซึ่งนั่นคือผลของการ query ที่เราต้องการจริงๆ ถึงจะนำไปเขียนเป็น query ได้ครับ
ปล. ผมได้เขียนบทความเกี่ยวกับการ Optimization โดยยกกรณีของคุณเป็นตัวอย่าง ผิดพลาดประการใดโปรดแจ้งด้วยนะครับ
Sun 23 Dec 2007 13:36:15
Mon 24 Dec 2007 23:15:37
Tue 25 Dec 2007 19:25:57
| no | id | date | ID_NEW |
| 1 | 5031018745 | 2007-11-01 | 503101 |
| 2 | 4921033214 | 2007-11-05 | 492103 |
Tue 25 Dec 2007 21:36:35
Sat 20 Sep 2008 23:32:35
Sun 21 Sep 2008 02:36:59
