#title 테이블의 값에 대한 기본 통계 http://book.naver.com/bookdb/book_detail.nhn?bid=3105641 에서 발췌 {{{ SELECT REPLACE(REPLACE(REPLACE(' SELECT '''' as colname, COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as VARCHAR) as minval, SUM(CASE WHEN = minval THEN freq ELSE 0 END) as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq = 1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT , COUNT(*) as freq FROM GROUP BY ) osum CROSS JOIN (SELECT MIN() as minval, MAX() as maxval, SUM(CASE WHEN IS NULL THEN 1 ELSE 0 END) as freqnull FROM (SELECT FROM ) osum) summary', '', column_name), '', table_name), '', (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 */ }}}