阿洪之聲

Subscribe:

2014年3月11日星期二

MySQL/Pivot table

"pivot table" or a "crosstab report"

(Note: this page needs to be wikified)

SQL Characteristic Functions: Do it without "if", "case", or "GROUP_CONCAT". Yes, there is use for this..."if" statements sometimes cause problems when used in combination.

The simple secret, and it's also why they work in almost all databases, is the following functions:

sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0 respectively
abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0
1-abs( sign( x) ) complement of the above, since this returns 1 only if x = 0

   Quick example:   sign(-1) = -1,  abs( sign(-1) ) = 1,  1-abs( sign(-1) ) = 0

Data for full example:

      CREATE TABLE exams (
        pkey int(11) NOT NULL auto_increment,
        name varchar(15),
        exam int,
        score int,
        PRIMARY KEY  (pkey)
      );

      insert into exams (name,exam,score) values ('Bob',1,75);
      insert into exams (name,exam,score) values ('Bob',2,77);
      insert into exams (name,exam,score) values ('Bob',3,78);
      insert into exams (name,exam,score) values ('Bob',4,80);

      insert into exams (name,exam,score) values ('Sue',1,90);
      insert into exams (name,exam,score) values ('Sue',2,97);
      insert into exams (name,exam,score) values ('Sue',3,98);
      insert into exams (name,exam,score) values ('Sue',4,99);

mysql> select * from exams;
+------+------+------+-------+
| pkey | name | exam | score |
+------+------+------+-------+
|    1 | Bob  |    1 |    75 |
|    2 | Bob  |    2 |    77 |
|    3 | Bob  |    3 |    78 |
|    4 | Bob  |    4 |    80 |
|    5 | Sue  |    1 |    90 |
|    6 | Sue  |    2 |    97 |
|    7 | Sue  |    3 |    98 |
|    8 | Sue  |    4 |    99 |
+------+------+------+-------+
8 rows in set (0.00 sec)

mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

Note, the above pivot table was created with one select statement.

Let's decompose to make the trick clearer, for the second exam:

mysql> select name, score, exam, exam-2, sign(exam-2), abs(sign(exam-2)), 1-abs(sign(exam-2)),
       score*(1-abs(sign(exam-2))) as exam2 from exams;
+------+-------+------+--------+--------------+-------------------+---------------------+-------+
| name | score | exam | exam-2 | sign(exam-2) | abs(sign(exam-2)) | 1-abs(sign(exam-2)) | exam2 |
+------+-------+------+--------+--------------+-------------------+---------------------+-------+
| Bob  |    75 |    1 |     -1 |           -1 |                 1 |                   0 |     0 |
| Bob  |    77 |    2 |      0 |            0 |                 0 |                   1 |    77 |
| Bob  |    78 |    3 |      1 |            1 |                 1 |                   0 |     0 |
| Bob  |    80 |    4 |      2 |            1 |                 1 |                   0 |     0 |
| Sue  |    90 |    1 |     -1 |           -1 |                 1 |                   0 |     0 |
| Sue  |    97 |    2 |      0 |            0 |                 0 |                   1 |    97 |
| Sue  |    98 |    3 |      1 |            1 |                 1 |                   0 |     0 |
| Sue  |    99 |    4 |      2 |            1 |                 1 |                   0 |     0 |
+------+-------+------+--------+--------------+-------------------+---------------------+-------+
8 rows in set (0.00 sec)

You may think IF's would be clean but WATCH OUT! Look what the following gives (INCORRECT !!):

mysql> select name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |  NULL |  NULL |  NULL |
| Sue  |    90 |  NULL |  NULL |  NULL |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

Note: the following does work - is all the maths necessary after all?

mysql> SELECT name,
       SUM(IF(exam=1,score,NULL)) AS exam1,
       SUM(IF(exam=2,score,NULL)) AS exam2,
       SUM(IF(exam=3,score,NULL)) AS exam3,
       SUM(IF(exam=4,score,0)) AS exam4
       FROM exams GROUP BY name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)


mysql> select name,
       sum(score*(1-abs(sign(exam-1)))) as exam1,
       sum(score*(1-abs(sign(exam-2)))) as exam2,
       sum(score*(1-abs(sign(exam-3)))) as exam3,
       sum(score*(1-abs(sign(exam-4)))) as exam4,
         sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
         sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
         sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3)))) as delta_3_4
       from exams group by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| Bob  |    75 |    77 |    78 |    80 |         2 |         1 |         2 |
| Sue  |    90 |    97 |    98 |    99 |         7 |         1 |         1 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

Above delta_1_2 shows the difference between the first and second exams, with the numbers being positive because both Bob and Sue improved their score with each exam. Calculating the deltas here shows it's possible to compare two rows, not columns which is easily done with the standard SQL statements but rows in the original table.

mysql>select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
  sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
  sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
  sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,

  sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1))))  +
  sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2))))  +
  sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3))))  as TotalIncPoints
  from exams group by name;

+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| Bob  |    75 |    77 |    78 |    80 |         2 |         1 |         2 |              5 |
| Sue  |    90 |    97 |    98 |    99 |         7 |         1 |         1 |              9 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
2 rows in set (0.00 sec)

TotalIncPoints shows the sum of the deltas.

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
  sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
  sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
  sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,


  sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1))))  +
  sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2))))  +
  sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3))))  as TotalIncPoints,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG

from exams group by name;

+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints | AVG   |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| Bob  |    75 |    77 |    78 |    80 |         2 |         1 |         2 |              5 | 77.50 |
| Sue  |    90 |    97 |    98 |    99 |         7 |         1 |         1 |              9 | 96.00 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
2 rows in set (0.00 sec)

It's possible to combine Total Increasing Point TotalIncPoints with AVG. In fact, it's possible to combine all of the example cuts of the data into one SQL statement, which provides additional options for displaying data on your page

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))))/2  as AVG1_2,

(sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))))/2 as AVG2_3,

(sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/2 as AVG3_4,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG

from exams group by name;

+------+-------+-------+-------+-------+--------+--------+--------+-------+
| name | exam1 | exam2 | exam3 | exam4 | AVG1_2 | AVG2_3 | AVG3_4 | AVG   |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| Bob  |    75 |    77 |    78 |    80 |  76.00 |  77.50 |  79.00 | 77.50 |
| Sue  |    90 |    97 |    98 |    99 |  93.50 |  97.50 |  98.50 | 96.00 |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
2 rows in set (0.00 sec)

Exam scores are listing along with moving averages...again it's all with one select statement.

Good article on "Cross tabulations" or de-normalizing data to show stats: http://dev.mysql.com/tech-resources/articles/wizard/print_version.html

ADOdb (PHP) can generate pivot tables using PivotTableSQL().

For Perl, check DBIx-SQLCrosstab.

資料來源

如何用單一SELECT 語法完成樞紐分析表,

SELECT p1.*, ( p1.Sum_a1+p1.Sum_a2+p1.Sum_b1) as Sum_All
from (select item,
    SUM(CASE class WHEN 'a1' THEN qty ELSE 0 END) AS Sum_a1,
    SUM(CASE class WHEN 'a2' THEN qty ELSE 0 END) AS Sum_a2,
    SUM(CASE class WHEN 'b1' THEN qty ELSE 0 END) AS Sum_b1
FROM testdata as p
GROUP BY item WITH ROLLUP
) as p1

資料來源

MSSQL類似MySQL(GROUP_CONCAT)功能!

拜EXCEL所賜，現在做報表很多需求都是使用者從原來EXCEL表單轉換而來~

一般而言，如果我們有Group by需求的報表，絕大部分都是看彙總後的資料，像是AVG、SUM、COUNT…等等

或者使用者想看類似樞紐分析表資料長相的時候，SQL SERVER 2005 提供 PIVOT也能幫得上忙。

( 基本上要不是走入IT這行，我覺得EXCEL用的好，很多報表可以不用做的那麼辛苦 !!)

Anyway 在使用者最大情況下，IT小小工程師也只能儘量滿足需求。

某天在廠區工作的時候，就遇到這種情況，使用者想要的報表長相如下：

而實際在資料庫內的資料呈現則是如下圖：

一般來說，我們針對這種資料，都會使用矩陣來呈現報表，如下圖：

透過Reporting Service來製作矩陣報表，真的非常容易，但要呈現使用者需要的樣子，就得在SQL上面偷偷下工夫了。

拜古哥幫忙，剛好找到如下討論串

參考網址：http://social.msdn.microsoft.com/Forums/en/transactsql/thread/f09d4166-2030-41fe-b86e-392fbc94db53

那麼就照網址內容依樣畫葫蘆，首先來建立TABLE吧

步驟1：

CREATE TABLE [dbo].[DAPON_TB](
    [CITY] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
    [PRODUCT] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
    [STORE] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]

步驟2：在Reporting Service內新增一張報表，並且新增資料集，SQL語法則如下

SELECT G.CITY,G.PRODUCT,
        STUFF
        (
            (
             SELECT CAST(':' AS VARCHAR(MAX))+U.STORE
             FROM DAPON_TB AS U
             WHERE U.PRODUCT=G.PRODUCT                                                  
             
             FOR XML PATH('')
            )
            ,1
            ,1
            ,''
        ) AS STORES,G.STORE
    FROM DAPON_TB AS G

步驟3：如果資料集SQL沒下錯，就會像下圖一樣。

步驟4：請在工具箱拉一個矩陣到配置視窗上，並依照下圖方式將欄位資料放上

步驟5：完成後點預覽，即可看到結果，如下

結論：整個重點還是在如何把資料轉成如下圖長相，參考一下步驟2的SQL語法吧!

趨勢科技瞄準兒童網安

（中央社記者江明晏台北11日電）家長們對兒童網安的需求日漸升高，趨勢科技推出有「家長防護網」功能的資安軟體，呼籲家長，要多多關心兒童上網行為。

根據警政署統計，去年一整年全台就有近3萬人失蹤，光是今年1月就有2千多起案例，國中、小學兒童在上學途中失蹤的達109人，其中不乏有與使用社群媒體或網路上交友有關的案例。

趨勢科技呼籲家長，要多多關心兒童上網行為，並使用有「家長防護網」功能的資安軟體，幫孩子過濾不良網站、管理上網行為，事先避免兒童被網路不良人士拐騙、甚至失蹤的悲劇。

有鑑於家長們對兒童網安的需求，趨勢科技PC-cillin2014雲端版，貼心納入「家長防護網」功能，賦予忙碌的家長更多的網路防護權限，可保護孩子們的網路行為，例如，可依據孩子不同年齡層、設定不同的網站內容等級；可規定孩子上網時段、或每天可使用電腦的時數；還可依照平日、假日做不同的設定。

此外，針對沉迷網路遊戲的兒童，還可防止他們在非規定的時間內玩電腦遊戲或使用指定的軟體，最後，「家長防護網」還提供一目了然的分析圖表，讓家長知道孩子的所有使用行為、及孩子嘗試瀏覽的不良網站清單等。1030311

Google網路廣告獲利遭行動App侵蝕

作者：編譯楊宛盼╱綜合外電 | 聯合新聞網 – 2014年3月11日上午3:48

Google幾乎是網路搜尋的代名詞，但愈來愈多用戶轉向行動裝置，且大把時間都是花在應用程式（App）而非網路上，Google的廣告獲利面臨的挑戰日增。為因應變局，該公司推出在搜尋結果中納入相關App資訊的新策略，致力於開拓新財源。

Google過去靠網路蜘蛛（spider）抓取網路資料，以精密的演算法對搜尋結果進行排名，但行動裝置用戶大多使用App，這套方法派不上用場，威脅Google規模500億美元的廣告業務。

Google去年秋季因此推出拓展行動用戶的新搜尋策略，模仿Google網路版網頁，當用戶使用智慧手機搜尋時，除關鍵字相關資料外，還會顯示相關App下載連結並詳列其功能。

臉書和推特等科技業者也紛紛採取類似行動，向App收取廣告費用以換取廣告頁面，臉書新策略已見初步成效。Google的行動已獲數家App業者支持，包括維基百科、旅遊網站Expedia與電影資料庫IMDB的App連結，都會出現在搜尋結果中。

對智慧手機來說，App連結比網頁連結更實用，像是可依螢幕大小自動調整，並納入手機定位功能以提供更多資訊。Google計劃讓App開發商遞交App以建構搜尋資料庫。

資料來源

2014年3月9日星期日

Getting Started with Studio'13 Workshop (播放清單)

2014年3月7日星期五

從PHP網頁將資料匯出成Excel檔

[Dreamweaver]從PHP網頁將資料匯出成Excel檔

雖然網路上已經有重點說明，但對於習慣使用DW的我，還是不能符合需求....

自己試了很久....才試出我要的方式 -- 呼叫資料庫的資料，按連結轉成excel檔

做法：

資料庫名稱test，資料表ta和tb

資料表ta

aid	aclass	aname
1	1	蘋果
2	1	鳳梨
3	2	波蔡
4	2	小白菜
5	3	豬肉
6	3	牛肉
7	4	金魚
8	4	吳郭魚

資料表tb

aclass	classname
1	水果
2	蔬菜
3	肉類
4	魚類

1.將表格和頁首設定好，並做一個連結，作為下載excel的按鈕

匯出excel檔

td>

編號	種類	名稱< /td>

匯出excel

2.連到資料庫，新增資料集Recordset1，用進階選項，SQL內填入：

SELECT ta.aid, ta.aname, tb.classname FROM ta LEFT JOIN tb ON ta.aclass = tb.aclass GROUP BY ta.aid, ta.aname

新增一樣的資料集，名稱為Recordset2

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
}
return $theValue;
}
}

mysql_select_db($database_testbata, $testbata);
$query_Recordset1 = "SELECT ta.aid, ta.aname, tb.classname FROM ta LEFT JOIN tb ON ta.aclass = tb.aclass GROUP BY ta.aid, ta.aname";
$Recordset1 = mysql_query($query_Recordset1, $testbata) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

mysql_select_db($database_testbata, $testbata);
$query_Recordset2 = "SELECT ta.aid, ta.aname, tb.classname FROM ta LEFT JOIN tb ON ta.aclass = tb.aclass GROUP BY ta.aid, ta.aname";
$Recordset2 = mysql_query($query_Recordset2, $testbata) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

?>
...

3.將資料顯示於table內，重複區域

...

編號	種類	名稱< /td>
>

...

4.複製下面語法至內最後面

if ($_GET['act']=='download') {
downloadxls();
die();
}
function downloadxls(){
$filename="test.xls";
header("Content-disposition: filename=$filename");
header("Content-type: application/octetstream");
header("Pragma: no-cache");
header("Expires: 0");
}

5.複製table語法貼於function內；剪下php內的Recordset2部分，貼於function內；並做修改

function downloadxls(){

$query_Recordset2 = "SELECT ta.aid, ta.aname, tb.classname FROM ta LEFT JOIN tb ON ta.aclass = tb.aclass GROUP BY ta.aid, ta.aname";
$Recordset2 = mysql_query($query_Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
$filename="test.xls";
header("Content-disposition: filename=$filename");
header("Content-type: application/octetstream");
header("Pragma: no-cache");
header("Expires: 0");

echo "& lt;td>名稱

編號

種類

";
for ($i=0;$i<$totalRows_Recordset2;$i++)
{
$row_Recordset2 = mysql_fetch_array($Recordset2);
echo " ".$row_Recordset2['aid']."".$row_Recordset2['aname']."".$row_Recordset2['classname']." ";
$j=$i+1;

}
echo "

";
}

範例檔案

資料來源

訂閱：文章 (Atom)

Pages

阿洪之聲

Ads 468x60px

Labels

Popular Posts

推薦網站

2014年3月11日星期二

MySQL/Pivot table

如何用單一SELECT 語法完成樞紐分析表,

MSSQL類似MySQL(GROUP_CONCAT)功能!

趨勢科技瞄準兒童網安

Google網路廣告獲利遭行動App侵蝕

2014年3月9日星期日

Getting Started with Studio'13 Workshop (播放清單)

2014年3月7日星期五

從PHP網頁將資料匯出成Excel檔

[Dreamweaver]從PHP網頁將資料匯出成Excel檔

Blog Archive

標籤

總網頁瀏覽量

Pages

Ads 468x60px

Labels

Popular Posts

推薦網站

2014年3月11日 星期二

2014年3月9日 星期日

2014年3月7日 星期五

[Dreamweaver]從PHP網頁將資 料匯出成Excel檔

Blog Archive

標籤

總網頁瀏覽量

2014年3月11日星期二

2014年3月9日星期日

2014年3月7日星期五

[Dreamweaver]從PHP網頁將資料匯出成Excel檔