13日目SQL

33996 ワード
テキストリンク

ご希望のようにSQL-7


文1)各レンタル店(store)の映画在庫(inventory)数と全映画在庫数は?(grouping set) 
SELECT store_id, count(*)
  FROM inventory i 
 GROUP BY  
 GROUPING SETS(
 	(store_id),
 	()
 )


質問2)各レンタル店の映画在庫数と映画在庫総量は?(rollup) 
SELECT store_id, count(*)
  FROM inventory i 
 GROUP BY ROLLUP (store_id)


第三題)国(国)の都市(都市)の売上高、国(国)の売上高小計及び総売上高.(grouping set) 
SELECT country, city, sum(amount)
  FROM payment p 
 INNER JOIN customer c ON p.customer_id =c.customer_id 
 INNER JOIN address a ON c.address_id = a.address_id 
 INNER JOIN city c2 ON c2.city_id  = a.city_id 
 INNER JOIN country c3 ON c2.country_id = c3.country_id 
 GROUP BY 
GROUPING SETS (
	(country, city),
	(country),
	()
)
ORDER BY country, city


問題4)国(国)の都市(都市)の売上高,国(国)の売上小計,および総売上高.(rollup) 
SELECT country, city, sum(amount)
  FROM payment p 
 INNER JOIN customer c ON p.customer_id =c.customer_id 
 INNER JOIN address a ON c.address_id = a.address_id 
 INNER JOIN city c2 ON c2.city_id  = a.city_id 
 INNER JOIN country c3 ON c2.country_id = c3.country_id 
 GROUP BY ROLLUP (country, city)
ORDER BY country, city


質問5)映画俳優が出演する映画カウントと、すべての俳優が出演する映画カウントを合計して、一緒に見せましょう.
SELECT actor_id, count(DISTINCT fa.film_id)
  FROM film_actor fa 
 GROUP BY
GROUPING SETS (
	(actor_id),
	()
)


質問6)国、都市、国の顧客数をリストしてください.(grouping sets) 
SELECT country, city, count(*)
  FROM customer c
 INNER JOIN address a ON c.address_id = a.address_id 
 INNER JOIN city c2 ON a.city_id = c2.city_id 
 INNER JOIN country c3 ON c2.country_id = c3.country_id 
 GROUP BY 
GROUPING SETS (
	(country, city),
	(country),
	()
)
 ORDER BY country, city


問題7)映画で使われる言語と映画の公開年の映画数,および映画の公開年の映画数. 
SELECT release_year, language_id, count(*)
  FROM film f
 GROUP BY
GROUPING SETS (
	(language_id, release_year),
	(release_year)
)


問題8)年度、毎日の決算数量と年度の決算数量を与えてください.
  • 支払数は、支払のid数を表す. 
    • SELECT
		  EXTRACT (YEAR FROM p.payment_date) AS YEAR 
		, EXTRACT (MONTH FROM p.payment_date) AS MONTH
		, EXTRACT (DAY FROM p.payment_date)AS DAY
		, count(p.payment_id)
  FROM payment p 
 GROUP BY  GROUPING sets (
 		  ( EXTRACT (YEAR FROM p.payment_date)
 		  , EXTRACT (MONTH FROM p.payment_date)
 		  , EXTRACT (DAY FROM p.payment_date))
 		, ( EXTRACT (YEAR FROM p.payment_date)
 		  , EXTRACT (MONTH FROM p.payment_date))
 		, ( EXTRACT (YEAR FROM p.payment_date))
 )
 ORDER BY YEAR, MONTH, day


    問題9)支社では,活動顧客の数と活動顧客の数を併せて示す.
    支店およびアクティビティの有無については、customerテーブルを使用して説明します.
  • パケットセットを使用して解答してください. 
    • SELECT store_id, count(c.customer_id)
  FROM customer c
 WHERE c.active = 1
 GROUP BY
GROUPING SETS (
	  (store_id)
	, ()
)


    第十題)支社、アクティブ顧客の数とアクティブ顧客の数を一緒に展示します.
    支店およびアクティビティの有無については、customerテーブルを使用して説明します.
  • rollupで説明し、パケットセットとの相違を検証します. 
    • SELECT store_id, count(c.customer_id)
  FROM customer c
 WHERE c.active = 1
 GROUP BY ROLLUP (store_id)


    違いは,GROUPING SETSを用いてROLLUPを処理する場合,GROUPのターゲットが多ければ面倒であることである.

    解析関数バー


    特定の集合内で、結果個数を変更しない場合、その集合内で合計やカウントなどの関数を計算することができる.
    -インターンシップ環境-
    PRODUCT_GROUP


    PRODUCT


    解析関数の結果の例
    SELECT COUNT(*)
  FROM PRODUCT;


    集約関数は、集約の結果のみを出力します.
    統計結果と集合を一緒に見たい場合は,解析関数を用いる.
    SELECT COUNT(*) OVER() , A.*
  FROM PRODUCT A;


    統計と一緒に出てきました.

    AVG関数


    解析関数AVG()から解析関数を正式に学習する.その前に、分析関数の構文について簡単な理解があります.
    解析関数の構文
    SELECT   C1
       , 분석함수(C2,C3, ...) OVER (PARTITION BY C4 ORDER BY C5)
  FROM TABLE_NAME;
    使用したい解析関数を使用して、対象列を記入した後、PARTIONBYに評価基準列、ORDER BYに列を記入します.
    <実習>
    SELECT AVG(PRICE)
  FROM product;


    SELECT b.group_name, AVG(PRICE)
  FROM PRODUCT a 
 INNER JOIN PRODUCT_GROUP b 
    ON (a.group_id = b.group_id)
 GROUP BY 
 b.group_name;


    それぞれのユニットのAVGを組み合わせて表現
    配列バイトは、パケットの値のみを表します.
    では、各値にグループAVGを表示したい場合は、どうすればいいのでしょうか.
    このときはAVG関数を使います.
    SELECT    p.proudct_name 
	, p.price 
	, pg.group_name 
	, AVG(p.price) OVER (PARTITION BY pg.group_name)
  FROM PRODUCT p
 INNER JOIN product_group pg
    ON (p.group_id = pg.group_id)


    計算結果は次のとおりです.
    また、累積合計を求めることもできます.
    <累計合計練習>
    SELECT    p.proudct_name 
	, p.price 
	, pg.group_name 
	, AVG(p.price) OVER (PARTITION BY pg.group_name ORDER BY p.price)		
  FROM PRODUCT p
 INNER JOIN product_group pg
    ON (p.group_id = pg.group_id)
    ORDER BYが追加されました.このために合計を積算します.


    各線が下に下がるほど、新しい平均数が求められます.

    ROW NUMBER、RANK、DENSE RANK関数


    特定の集合内で結果個数を変えずにその集合内で特定の列の順序の関数を求める.
    ROW NUMBER関数実習-必ず1.2.3.4.5...
    SELECT 	  p.proudct_name 
	, pg.group_name 
	, p.price 
	, ROW_NUMBER () OVER
	( PARTITION BY pg.group_name ORDER BY p.price)
  FROM product p
 INNER JOIN product_group pg 
         ON (p.group_id = pg.group_id)


    その結果,group name間でパケットソートを行い,ランキングとした.昇順でソートした結果値が表示されます.
    Sony VAIOとレノボThinkpadの価格は同じですが、順位は違います.これはROW NUMBERの特性です.
    ROW NUMBERは同じ順位でも無条件に順番に並びます.
    では、同じ順位によって同じ順位を与える関数もあるのではないでしょうか.
    RANK関数!
    RANK関数の練習-同じランキングの場合は、次のランキング1、1、3、4をスキップします.
    SELECT 	  p.proudct_name 
	, pg.group_name 
	, p.price 
	, RANK () OVER
    	( PARTITION BY pg.group_name ORDER BY p.price)
  FROM product p
 INNER JOIN product_group pg 
         ON (p.group_id = pg.group_id)


    RANKは同じ順位があれば、同じ順位で次へジャンプします.
    次のランキングに入らない関数もあります.
    DENSE RANK関数
    SELECT 	  p.proudct_name 
		, pg.group_name 
		, p.price 
		, DENSE_RANK () OVER
			( PARTITION BY pg.group_name ORDER BY p.price)
  FROM product p
 INNER JOIN product_group pg 
         ON (p.group_id = pg.group_id)


    DENSE RANKは同じ順位があれば同じ順位で次をスキップしません.

    FIRST VALUE、LAST VALUE関数


    特定の集合内で、結果の個数を変更しない場合、その集合内の特定の列の最初の値または最後の値の関数を求める.
    FIRST_VALUE
    これは最初の値を求めます.
    グループごとに最低価格の品物の価格を一緒に印刷します
    SELECT  p.proudct_name, pg.group_name, p.price
      , FIRST_VALUE (p.price) OVER
      (PARTITION BY pg.group_name ORDER BY p.price 
      ) 
      AS LOWEST_PRICE_PER_GROUP
  FROM product p
 INNER JOIN product_group pg 
         ON (p.group_id = pg.group_id)


    各グループの最小値は右端に書かれています.
    LAST_VALUE
    最後の値を求めることです.
    グループごとに最も価値のある品物の価格を一括して印刷する
    SELECT  p.proudct_name, pg.group_name, p.price
      , LAST_VALUE (p.price) OVER
      (PARTITION BY pg.group_name ORDER BY p.price 
      RANGE BETWEEN UNBOUNDED PRECEDING 
      AND UNBOUNDED FOLLOWING)
      AS HIGHEST_PRICE_PER_GROUP
  FROM product p
 INNER JOIN product_group pg 
         ON (p.group_id = pg.group_id)


    FIRST VALUEとは異なり、LAST VALUEは範囲を記入する必要があります.
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUND FOLLOWINGS
    グループの最初の値のうち最後の値を範囲とすることを意味します.
    範囲が狭くなければどうなりますか?
    SELECT  p.proudct_name, pg.group_name, p.price
      , LAST_VALUE (p.price) OVER
      (PARTITION BY pg.group_name ORDER BY p.price 
      )
      AS HIGHEST_PRICE_PER_GROUP
  FROM product p
 INNER JOIN product_group pg 
         ON (p.group_id = pg.group_id)


    各グループの最後の値ではなく、自分の値です.
    これは、LAST VALUEのデフォルト値がCURRENT ROWであるためです.
    つまり上のコードはこれと同じです.
    SELECT  p.proudct_name, pg.group_name, p.price
      , LAST_VALUE (p.price) OVER
      (PARTITION BY pg.group_name ORDER BY p.price 
      RANGE BETWEEN UNBOUNDED PRECEDING 
      		AND CURRENT ROW 
      )
      AS HIGHEST_PRICE_PER_GROUP
  FROM product p
 INNER JOIN product_group pg 
         ON (p.group_id = pg.group_id)
    最後の値は現在の値です.現在の行の範囲のみが指定されているためです.
    LAST VALUEの場合
    RANGE BETWEEN UNBOUNDED PRECEDINGとUNBOUNF FOLLOWINGを明記する
    reactor解剖術(2)
    CK 001 | helloBot(javaScript)