データベース

データベースに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Microsoft SQL Server)
SQLServerでクロス集計のような結果を取得したい2
投稿日時: 17/07/08 21:09:39
投稿者: TI

こんばんは。
 
SQLServerxpressを使用しています。
 
勤怠情報の月別の集計をしたいと考えています。
 
勤怠テーブル
社員ID        日付        勤怠内容ID 
1        2017/4/1    1
1        2017/4/5    2
2        2017/4/10    3
2        2017/4/15    4
1        2017/4/30    3
2        2017/4/29    2
1        2017/5/3    1
1        2017/5/10    3
2        2017/5/2    2
 
 
勤怠内容マスター
勤怠内容ID    勤怠内容    有給カウント    欠勤カウント
1        有給        1                0    
2        半日有給    0.5                0
3        欠勤        0                1
4        有給振替    1                1
 
 
テーブルは上記の構成になっており、指定した期間における職員ごとの
各勤怠内容のカウントの集計を下記の形で取り出したいと思います。
 
勤怠内容        4月        5月        6月
有給            1.5        1        
欠勤            1.5        2.0        
 
 
3日間ほど悩んでいるのですが、全く糸口すら見えてきません。
 
現状ではExcelなどに一旦出力して、整形をしたほうがよいかと思い、
その方向でも進めているのですが、もしSQLで行うことができるのであれば、
今後の為にも知っておきたいと思っています。
 
どうか、アドバイスをお願いします。

回答
投稿日時: 17/07/09 11:15:57
投稿者: hatena
投稿者のウェブサイトに移動

TI さんの引用:
3日間ほど悩んでいるのですが、全く糸口すら見えてきません。

 
「SQLServer クロス集計」でWEB検索してみてください。
 pivot句をつかえばいいということがすぐわかります。
数分で解決します。

投稿日時: 17/08/13 06:01:45
投稿者: TI

おはようございます。
本業が忙しく、なかなかご返信ができませんでした。
 
ようやく以下のように記述をするところまで進みました。
 
しかし、勤怠内容マスターのうち、有給カウントと欠勤カウント両方が
あるデータの集計をどのようにすればよいかで止まってしまいました。
 
何か良い方法があれば、アドバイスをお願いします。
 
DECLARE @startdate SMALLDATETIME
DECLARE @enddate SMALLDATETIME
 
    SET @startdate = '2016/4/1'
    SET @enddate = '2016/7/31'
    
        SELECT
            社員ID,
            勤怠内容,
            CONVERT(VARCHAR(6), 日付, 112) AS 年月,
            有給,
            欠勤
        FROM
            勤怠テーブル
    
                INNER JOIN
                    勤怠内容マスター
                ON
                    勤怠内容 = mkt_code
        
        PIVOT
            (
                SUM(有給カウント)
                FOR 勤怠内容
                IN (有給, 欠勤)
            ) AS PV
        
        WHERE
            日付 BETWEEN @startdate AND @enddate
        GROUP BY
            社員ID,
            勤怠内容,
            CONVERT(VARCHAR(6), 日付, 112),
            PV.有給,
            PV.欠勤

回答
投稿日時: 17/08/13 15:57:40
投稿者: hatena
投稿者のウェブサイトに移動

TI さんの引用:
しかし、勤怠内容マスターのうち、有給カウントと欠勤カウント両方が
あるデータの集計をどのようにすればよいかで止まってしまいました。

 
テーブルの正規化ができてないのが複雑になる原因ですね。
 
勤怠内容マスターを下記のように分割すればシンプルにできます。
 
勤怠内容マスター 
勤怠内容ID  勤怠内容
 1     有給   
 2         半日有給 
 3         欠勤 
 4         有給振替 

  
勤怠カウント
勤怠内容ID  種別  カウント
 1     有給C  1
 2     有給C  0.5
 3     欠勤C  1
 4     有給C  1
 4     欠勤C  1

 
勤怠テーブル と 勤怠カウント を結合させてPIVOTで行列変換してください。

回答
投稿日時: 17/08/16 22:43:12
投稿者: mayu.

こんばんは。
 

引用:
ようやく以下のように記述をするところまで進みました。

掲載いただいたSQL文は、エラー終了するのではないでしょうか。
PIVOTを利用するにあたり、まずは MS公式のTechNetライブラリをご覧になって
仕様と制限を把握なさっておくことをお薦めします。
特殊なのは以下の2点でしょう。
 
【 1 】
PIVOT演算子は FROM句 の一部になっており、PIVOTを利用した場合のFROM句は
 { 列見出しと行見出しに使用する列のみで構成された結果セット }
である必要があります。
よって FROM句には、インラインビュー( サブクエリ )の組み込みが ほぼ必須になり、
JOINによる結合もインラインビューの中で完結させてしまいます。
 
【 2 】
GROUP BY 句は必要ありません。
選択した列のうち、IN句で指定した見出し以外の列は 自動でグループ化されます。
 
引用:
勤怠内容        4月        5月        6月
有給            1.5        1        
欠勤            1.5        2.0

ご希望の結果は、PIVOT( fn .. FOR .. IN ) でも可能ですし
MS-Accessと同じ構文である集計クエリでも可能です。
 
/* ■ 集計クエリ版 */
--------------------------------------------------------------------
SELECT y.社員ID
     , year( dateadd( month, -3, y.日付 ) ) As 年
     , x.種別
     , sum( iif( month( y.日付 ) = 4, x._count, null ) ) As _4
     , sum( iif( month( y.日付 ) = 5, x._count, null ) ) As _5
     , sum( iif( month( y.日付 ) = 6, x._count, null ) ) As _6
     , sum( iif( month( y.日付 ) = 7, x._count, null ) ) As _7
     , sum( iif( month( y.日付 ) = 8, x._count, null ) ) As _8
     , sum( iif( month( y.日付 ) = 9, x._count, null ) ) As _9
     , sum( iif( month( y.日付 ) = 10, x._count, null ) ) As _10
     , sum( iif( month( y.日付 ) = 11, x._count, null ) ) As _11
     , sum( iif( month( y.日付 ) = 12, x._count, null ) ) As _12
     , sum( iif( month( y.日付 ) = 1, x._count, null ) ) As _1
     , sum( iif( month( y.日付 ) = 2, x._count, null ) ) As _2
     , sum( iif( month( y.日付 ) = 3, x._count, null ) ) As _3
FROM
(
    SELECT 勤怠内容ID
         , '有給' As 種別
         , 有給カウント As _count
    FROM 勤怠内容マスター
    UNION ALL
    SELECT 勤怠内容ID
         , '欠勤'
         , 欠勤カウント
    FROM 勤怠内容マスター
) x
INNER JOIN 勤怠テーブル y
        ON x.勤怠内容ID = y.勤怠内容ID
-- WHERE year( dateadd( month, -3, y.日付 ) ) = year( getdate() )
GROUP BY y.社員ID
       , year( dateadd( month, -3, y.日付 ) )
       , x.種別
ORDER BY 1, 2, 3 ;
--------------------------------------------------------------------
 
/* ■ PIVOT版 */
--------------------------------------------------------------------
WITH cte ( 社員ID, 年, 月, 種別, カウント )
AS
(
    SELECT y.社員ID
         , year( dateadd( month, -3, y.日付 ) )
         , format( y.日付, '_M' )
         , x.種別
         , x._count
    FROM
    (
        SELECT 勤怠内容ID
             , '有給' 種別
             , 有給カウント _count
        FROM 勤怠内容マスター
        UNION ALL
        SELECT 勤怠内容ID
             , '欠勤'
             , 欠勤カウント
        FROM 勤怠内容マスター
    ) x
    INNER JOIN 勤怠テーブル y
            ON x.勤怠内容ID = y.勤怠内容ID
    -- WHERE year( dateadd( month, -3, y.日付 ) ) = year( getdate() )
)
SELECT * FROM cte
PIVOT
(
    sum( カウント )
    FOR 月
    IN ( _4, _5, _6, _7, _8, _9, _10, _11, _12, _1, _2, _3 )
) pv
ORDER BY 1, 2, 3 ;
--------------------------------------------------------------------
 
※ SQL文中の UNIONで疑似正規化している部分は
  hatenaさんが仰っているように、設計を見直せば不要になります。

トピックに返信