[ADO] 개봉된 영화 중 분기별 매출과 개봉 영화수를 구해보자

https://1stminokingdom.tistory.com/117

[ADO] sql : Pivot 함수를 이용하자

이전글에 연장선이다.

이번에는 개봉된 영화 중 분기별 매출과 개봉 영화수를 구하는 구문을 알아보도록 하자..

 

mySql에는 extract함수가 있어서 매개변수에 따라

mysql> SELECT EXTRACT(YEAR_MONTH FROM '2011-01-09'); -> 201101

mysql> SELECT EXTRACT(DAY_HOUR FROM '2011-01-09 13:32:03'); -> 0913

 

이런식의 조합결과가 나오지만 VBA + adoDB에서는 불가하다.

또한 분기를 표현하는 QUARTER 함수 역시 사용할 수가 없어서

DATEPART('q' ,개봉일) 이렇게 해서 분기값을 반환받아야 한다.

 

개봉된 영화를 카운트 하기위해선 우리에게 너무나 익숙한 COUNT(*) 를 사용할 수 있는데  * 는 COUNT함수에서만 사용가능하다.

 

WHERE DATEPART('Q', 개봉일) = 1 의 의미는 개봉일에서 1분기에 해당하는 값들을 의미한다.

AND YEAR(개봉일) = " & strYear 의 의미는 개봉일이 입력받은 년도와 분기가 맞는 값들을 의미한다.

GROUP BY YEAR(개봉일) , MONTH(개봉일) 의 의미는 이 두값을 그룹으로 묶어서 COUNT(*) 영화수를 구하고

SUM(매출액)을 하기 위한 구문이다.

 

해서 위 구문을 풀어보면

SELECT : 

개봉일에서 년도만 추출한 컬럼명을 [년도]로해라

개봉일에서 월만 추출한 컬럼명을 [월별]로해라

그룹한것들의 개별 카운팅한 것들을 [개봉편수]로해라

그룹한것들의 매출합계를 합것을  [누계매출]로해라

 

FROM:

박스오피스 시트에서 가지고 와라

 

WHERE

조건은 분기와 해당 년도가 맞는것들만 가져와라

 

GROUP BY

그룹은 년도와 월을 그룹화하여 구해라

Option Explicit

Sub Haja_Guid_Pivot()

    Dim StrGuid$: StrGuid = "{B691E011-1797-432E-907A-4D8C69339129}" '= ADODB참조
    
    On Error Resume Next
        ThisWorkbook.VBProject.References.AddFromGuid StrGuid, 0, 0 '= 가장 최신버전
    On Error GoTo 0
    
    Call Haja_Adodb_Pivot

End Sub
Sub Haja_Adodb_Pivot()

    Dim Rs As New ADODB.Recordset
    Dim strPath$
    Dim strSQL$, strConn$
    Dim strYear&
    Dim i&
    
    strPath = ThisWorkbook.FullName
    
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & strPath & ";" & _
              "Extended Properties=Excel 12.0;"
              
    strYear = Sheets("박스오피스").[m2]          '= 변경하는 년도 값
    
    strSQL = " SELECT YEAR(개봉일) AS 년도, MONTH(개봉일) AS 월별, COUNT(*) AS 개봉편수, SUM(매출액) as 누계매출 " & _
             " FROM [박스오피스$] " & _
             " WHERE DATEPART('Q', 개봉일) =1 " & _
             " AND YEAR(개봉일) = " & strYear & _
             " GROUP BY YEAR(개봉일) , MONTH(개봉일) ; "
             

    Rs.Open strSQL, strConn
    
    With Sheets("연도별")
        
        .Cells.Clear
        
        For i = 0 To Rs.Fields.Count - 1
        
            .Cells(1, i + 1) = Rs.Fields(i).Name
        Next i
    
        If Rs.EOF Then
            MsgBox "조건에 맞는 데이터가 없습니다."
            Rs.Close
            Set Rs = Nothing
            Exit Sub
        Else
            .[a2].CopyFromRecordset Rs
           
        End If

           With .UsedRange
                .NumberFormat = "#,##0"
                 Sheets("박스오피스").[a1].Copy
                 Range(Sheets("연도별").[a1], Sheets("연도별").[a1].End(2)).PasteSpecial Paste:=xlPasteFormats
                 Sheets("연도별").[a1].CurrentRegion.Borders.LineStyle = 1
                 Sheets("연도별").[a1].CurrentRegion.HorizontalAlignment = xlCenter
                .Font.Size = 9
                .Columns.AutoFit
                Sheets("연도별").Activate
           End With
           
      End With
         
    Rs.Close
    Set Rs = Nothing
End Sub

영화(분기별).xlsm

0.05MB