http://book.naver.com/bookdb/book_detail.nhn?bid=3105641 覦豬
SELECT REPLACE(REPLACE(REPLACE('<start> SELECT ''<col>'' as colname,
COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as
VARCHAR) as minval, SUM(CASE WHEN <col> = minval THEN freq ELSE 0 END)
as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN
<col> = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =
1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT <col>, COUNT(*) as freq
FROM <tab> GROUP BY <col>) osum CROSS JOIN (SELECT MIN(<col>) as minval,
MAX(<col>) as maxval, SUM(CASE WHEN <col> IS NULL THEN 1 ELSE 0 END) as
freqnull FROM (SELECT <col> FROM <tab>) osum) summary',
'<col>', column_name),
'<tab>', table_name),
'<start>',
(CASE WHEN ordinal_position = 1 THEN ''
ELSE 'UNION ALL' END))
FROM (SELECT table_name, column_name, ordinal_position
FROM information_schema.columns
WHERE table_name = 'orders') a
-- 貎朱Μ襯 ろ 蟆郁骸 .. 襯 ろ覃...貉殊 蠍磯蓋 糾
SELECT 'orderid' as colname, COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as VARCHAR) as minval, SUM(CASE WHEN orderid = minval THEN freq ELSE 0 END) as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN orderid = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq = 1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT orderid, COUNT(*) as freq FROM orders GROUP BY orderid) osum CROSS JOIN (SELECT MIN(orderid) as minval, MAX(orderid) as maxval, SUM(CASE WHEN orderid IS NULL THEN 1 ELSE 0 END) as freqnull FROM (SELECT orderid FROM orders) osum) summary
UNION ALL SELECT 'customerid' as colname, COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as VARCHAR) as minval, SUM(CASE WHEN customerid = minval THEN freq ELSE 0 END) as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN customerid = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq = 1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT customerid, COUNT(*) as freq FROM orders GROUP BY customerid) osum CROSS JOIN (SELECT MIN(customerid) as minval, MAX(customerid) as maxval, SUM(CASE WHEN customerid IS NULL THEN 1 ELSE 0 END) as freqnull FROM (SELECT customerid FROM orders) osum) summary
UNION ALL SELECT 'campaignid' as colname, COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as VARCHAR) as minval, SUM(CASE WHEN campaignid = minval THEN freq ELSE 0 END) as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN campaignid = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq = 1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT campaignid, COUNT(*) as freq FROM orders GROUP BY campaignid) osum CROSS JOIN (SELECT MIN(campaignid) as minval, MAX(campaignid) as maxval, SUM(CASE WHEN campaignid IS NULL THEN 1 ELSE 0 END) as freqnull FROM (SELECT campaignid FROM orders) osum) summary
UNION ALL SELECT 'orderdate' as colname, COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as VARCHAR) as minval, SUM(CASE WHEN orderdate = minval THEN freq ELSE 0 END) as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN orderdate = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq = 1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT orderdate, COUNT(*) as freq FROM orders GROUP BY orderdate) osum CROSS JOIN (SELECT MIN(orderdate) as minval, MAX(orderdate) as maxval, SUM(CASE WHEN orderdate IS NULL THEN 1 ELSE 0 END) as freqnull FROM (SELECT orderdate FROM orders) osum) summary
UNION ALL SELECT 'city' as colname, COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as VARCHAR) as minval, SUM(CASE WHEN city = minval THEN freq ELSE 0 END) as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN city = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq = 1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT city, COUNT(*) as freq FROM orders GROUP BY city) osum CROSS JOIN (SELECT MIN(city) as minval, MAX(city) as maxval, SUM(CASE WHEN city IS NULL THEN 1 ELSE 0 END) as freqnull FROM (SELECT city FROM orders) osum) summary
UNION ALL SELECT 'state' as colname, COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as VARCHAR) as minval, SUM(CASE WHEN state = minval THEN freq ELSE 0 END) as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN state = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq = 1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT state, COUNT(*) as freq FROM orders GROUP BY state) osum CROSS JOIN (SELECT MIN(state) as minval, MAX(state) as maxval, SUM(CASE WHEN state IS NULL THEN 1 ELSE 0 END) as freqnull FROM (SELECT state FROM orders) osum) summary
UNION ALL SELECT 'zipcode' as colname, COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as VARCHAR) as minval, SUM(CASE WHEN zipcode = minval THEN freq ELSE 0 END) as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN zipcode = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq = 1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT zipcode, COUNT(*) as freq FROM orders GROUP BY zipcode) osum CROSS JOIN (SELECT MIN(zipcode) as minval, MAX(zipcode) as maxval, SUM(CASE WHEN zipcode IS NULL THEN 1 ELSE 0 END) as freqnull FROM (SELECT zipcode FROM orders) osum) summary
UNION ALL SELECT 'paymenttype' as colname, COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as VARCHAR) as minval, SUM(CASE WHEN paymenttype = minval THEN freq ELSE 0 END) as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN paymenttype = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq = 1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT paymenttype, COUNT(*) as freq FROM orders GROUP BY paymenttype) osum CROSS JOIN (SELECT MIN(paymenttype) as minval, MAX(paymenttype) as maxval, SUM(CASE WHEN paymenttype IS NULL THEN 1 ELSE 0 END) as freqnull FROM (SELECT paymenttype FROM orders) osum) summary
UNION ALL SELECT 'totalprice' as colname, COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as VARCHAR) as minval, SUM(CASE WHEN totalprice = minval THEN freq ELSE 0 END) as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN totalprice = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq = 1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT totalprice, COUNT(*) as freq FROM orders GROUP BY totalprice) osum CROSS JOIN (SELECT MIN(totalprice) as minval, MAX(totalprice) as maxval, SUM(CASE WHEN totalprice IS NULL THEN 1 ELSE 0 END) as freqnull FROM (SELECT totalprice FROM orders) osum) summary
UNION ALL SELECT 'numorderlines' as colname, COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as VARCHAR) as minval, SUM(CASE WHEN numorderlines = minval THEN freq ELSE 0 END) as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN numorderlines = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq = 1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT numorderlines, COUNT(*) as freq FROM orders GROUP BY numorderlines) osum CROSS JOIN (SELECT MIN(numorderlines) as minval, MAX(numorderlines) as maxval, SUM(CASE WHEN numorderlines IS NULL THEN 1 ELSE 0 END) as freqnull FROM (SELECT numorderlines FROM orders) osum) summary
UNION ALL SELECT 'numunits' as colname, COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as VARCHAR) as minval, SUM(CASE WHEN numunits = minval THEN freq ELSE 0 END) as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN numunits = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq = 1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT numunits, COUNT(*) as freq FROM orders GROUP BY numunits) osum CROSS JOIN (SELECT MIN(numunits) as minval, MAX(numunits) as maxval, SUM(CASE WHEN numunits IS NULL THEN 1 ELSE 0 END) as freqnull FROM (SELECT numunits FROM orders) osum) summary
/*
colname numvalues freqnull minval numminvals maxval nummaxvals numuniques
------------- ----------- ----------- ------------------------------ ----------- ------------------------------ ----------- -----------
orderid 192983 0 999992 1 1643157 1 192983
customerid 189560 0 0 3424 189559 1 189559
campaignid 239 0 2001 5 2239 4 24
orderdate 2541 0 10 4 2009 12:00AM 181 09 20 2016 12:00AM 2 0
city 12825 17 #07-01 FONTANA HGTS 1 ZURICH 5 6318
state 92 1119 . 2 YU 2 14
zipcode 15579 144 - 1 Z5B2T 1 5954
paymenttype 6 0 ?? 313 VI 77017 0
totalprice 7653 0 0.00 9128 9848.96 1 4115
numorderlines 41 0 1 139561 150 1 14
numunits 142 0 1 127914 19527 1 55
*/