DB의 SQL 문으로 분석하기
앞페이지에서 엑셀의 PivotTable로 데이타를 분석하였다
이번에는 DB의 SQL언어로 데이타를 가져오면서 분석을 해보자
실은 SQL언어에 능숙하다면 PivotTable도 필요없다고 해도 된다
어떤 사람은 피벗을 주면 , 어휴 뭐가 이렇게 복잡해...
그냥 월별로 애완동물별로 매출현황만 보여달라니까??!!! 라고 짜증을 낼수도 있다..
그러면 개발자는 이렇게 말하면 된다
그럼...보고 싶은 내용을 목록을 만들어 주시지요!!
그럼 만들어 드릴께...
(실은 하나도 어려운 것이 아니니까...금방 된다..SQL을 잘 알면)
그럼 숙제를 받은 보고 받는 사람은 목록을 작성하겠지..
아래와 같이..내가 보고 싶은 것은 말이야...
1)월별 애완동물별 매출현황
2)월별 고객별 매출현황
3)고객별 애완동물을 몇마리나 갖고 있는지 명단..
...
...
등과 같이 보고 싶은 것만 뽑아주기를 바랄것이다
이런 사람에게 피벗테이블을 주면 낯선 인터페이스에 경기를 일으킨다
DB는 정보의 블랙박스다..
모든 분석이 나온다
분석하고 싶은 생각만 확실하면
만약 수의사별로 월별 매출현황을 보고 싶다고 한다면????
앞에서 만든 테이블들에는 수의사정보는 없다..
그렇게 되면 DB설계를 추가하여야 하게 된다
그래서 DB설계는 몇번을 테스트해보고, 필요한 것이 또 생각나면 테이블이나
휠드를 추가하여 나가게 된다
그래서 DB설계는 확장을 전제하고 머리를 써야 하는 하나의 정보관리의 예술인 셈이다
그림과 같이 버튼을 만들어 붙이고 SQL언어로 재미있게 정보를 분석하면서
불러 오면 된다
말하는대로 분석이 되는 SQL언어...
이 시리즈를 통하여 완전 접수하시기를...
이것을 하면 나중에 VB.Net의 유사한 문법인 LINQ라는 언어는 거저 먹는다
아무튼 그림같은 버튼을 만들어주면 고객들은 좋아한다..
흠 간단하고 좋네...
이런 사람들에게 피벗테이블은 복잡하고 헷갈리고 열받는다
고객은 다양한 것이니, 피벗이 좋다고 피벗만 우긴다면
영...말이 안통한다는 소리를 듣게 된다
피벗을 원하는 사람에게는 피벗..
간단한 것을 원하는 사람이라면 아주 간단한 것으로..무엇이던지 받아줄수 있어야
유능한 개발자이다
버튼의 갯수가 늘어날때 열을 바꾸는 작업도 앞페이지에서 미쳐 처리하지 못한 것
이 페이지에서 하도록 하자
그리고 응용하셔서 앞의 페이지(피벗을 위한 콤보상자배열)에서 휠드갯수가 늘어남에 따른
콤보상자의 위치를 조정하시기를..
그런데 사용자가 요구하는 분석이 다양할수 있는데 이것을 아래와 같이 버튼을 일일이
만들어 넣기에는 너무 번거롭다..
Dim WithEvents oBtnSQL_MonthPetSales As MSForms.CommandButton
Dim WithEvents oBtnSQL_MonthCustSales As MSForms.CommandButton
Dim WithEvents oBtnSQL_CustPetSales As MSForms.CommandButton
Dim WithEvents oBtnSQL_PetSales As MSForms.CommandButton
Dim WithEvents oBtnSQL_MonthSales As MSForms.CommandButton
....
....
....
사용자의 요구에 따라서 얼마나 만들어질지 알수가 없다...
한두개면 WithEvents로 하여 만들면 좋겠지만, 몇개가 앞으로 만들어질지
모르니 크래스모듈을 하나 만드는 것이 좋고
이 크래스모듈을 생성한 개체를 집합체(배열)에 담아 전역변수로 선언하는 것이 좋겠다
아래와 같이 버튼이 몇개생기던 버튼을 만들면서 이벤트를 활용하게 할수 있겠다
'' 만들고 싶은 버튼을 배열화하여 호출한다
creatControlsForSQL_ Array("월별동물별매출", "월별고객별매출", "고객별동물별매출", _
"동물별매출", "월별매출", "A1", "A_2", "A_3", "A_4", "A_5", "A_6", "A_7", "A_8", "A_9", "A_10")
Sub creatControlsForSQL_(arrCaption As Variant)
Dim iBtnNum As Integer
Dim iNextBtn As Integer
Const StartX As Integer = 6
Const StartY As Integer = 6
Const Width As Integer = 150
Const HEIGHT As Integer = 20
Dim iCol As Integer
Dim iRowNum As Integer
Dim oBtn As MSForms.CommandButton
iBtnNum = UBound(arrCaption)
For iNextBtn = 0 To iBtnNum
'' 버튼 생성하고
Set oBtn = Me.MultiPage1.Pages(3).Controls.Add("Forms.Commandbutton.1", "cmd_" & arrCaption(iNextBtn))
'' 전역 동적배열변수를 확장해 나가면서..
ReDim Preserve oSQLbuttonList(iNextBtn)
'' clsButton 즉 크래스모듈로 개체를 만들고
Set oSQLbuttonList(iNextBtn) = New clsButton
'' clsButton개체가 갖고 있는 oButton 에 위에서 만든 oBtn개체를 준다
Set oSQLbuttonList(iNextBtn).oButton = oBtn
With oBtn
.Caption = arrCaption(iNextBtn)
.Width = Width
.HEIGHT = HEIGHT
If (StartY + iRowNum * HEIGHT) > Me.MultiPage1.HEIGHT - 30 Then
iCol = iCol + 1
iRowNum = 0
End If
.Top = StartY + iRowNum * HEIGHT
.Left = StartX + (iCol * (Width + 6))
End With
iRowNum = iRowNum + 1
Next
End Sub
SQL문의 파워....
앞페이지에서 이야기 하였던 SQL을 다시, 리뷰하는 셈이다
아래의 버튼을 크릭할때 마다 총알같이 정보를 불러다가 시트에 뿌린다
아래의 SQL문을 척보면 복잡해 보이지만 쥐뿔도 아니다
복잡해 보이는 것은 테이블이름과 가져오려는 휠드명등이 장황하게 들어가서 그렇다
그래서 Access의 쿼리테이블디자인 창을 이용하면 SQL문을 거져 가져오니..
또한 별것도 아니다
앞페이지에서 Access 쿼리디자인창사용설명을 잘 이해하시면 작성하는 것도
거저 먹는다
문제는 자신이 만들었던 혹은 남이 만들었던 테이블과 테이블간의 관계와 휠드명과
휠드의 정보타입들의 족보를 손에 쥐고 있으면 된다
Button 크래스모둘의 버튼 크릭이벤트프로시져...
Private Sub oButton_Click()
Dim sSQL As String
Dim oRst As Recordset
Dim shtReport As Worksheet
Dim iCol As Integer
Const SHT_RPT As String = "요약분석시트"
On Error Resume Next
sSQL = "SELECT "
Select Case oButton.Caption
Case "월별동물별매출"
sSQL = sSQL & "애완동물테이블.PetName, FORMAT(Month([WorkDate]),""00월"") AS Mon, Sum(진료타입테이블.Price) AS Total " & _
" FROM (진료진행테이블 INNER JOIN 애완동물테이블 ON 진료진행테이블.PetID = 애완동물테이블.PetID) " & _
" INNER JOIN 진료타입테이블 ON 진료진행테이블.TreatID = 진료타입테이블.TreatID " & _
" GROUP BY 애완동물테이블.PetName, Month([WorkDate]) ;"
Case "월별고객별매출"
sSQL = sSQL & "고객테이블.CustomerName, FORMAT(Month([WorkDate]),""00월"") AS Mon, Sum(진료타입테이블.Price) AS Total " & _
" FROM (진료진행테이블 INNER JOIN 고객테이블 ON 진료진행테이블.PetID = 고객테이블.CustomerID) " & _
" INNER JOIN 진료타입테이블 ON 진료진행테이블.TreatID = 진료타입테이블.TreatID " & _
" GROUP BY 고객테이블.CustomerName, Month([WorkDate]) ;"
Case "고객별동물별매출"
sSQL = sSQL & "고객테이블.CustomerName, 애완동물테이블.PetName, Sum(진료타입테이블.[Price]) AS Total " & _
" FROM (진료타입테이블 INNER JOIN (애완동물테이블 INNER JOIN 진료진행테이블 ON " & _
" 애완동물테이블.PetID = 진료진행테이블.PetID) ON " & _
" 진료타입테이블.TreatID = 진료진행테이블.TreatID) INNER JOIN 고객테이블 ON " & _
" 애완동물테이블.CustomerID = 고객테이블.CustomerID " & _
" GROUP BY 고객테이블.CustomerName, 애완동물테이블.PetName;"
Case "동물별매출"
sSQL = sSQL & "애완동물테이블.PetName, Sum(진료타입테이블.[Price]) AS Total " & _
" FROM 진료타입테이블 INNER JOIN (애완동물테이블 INNER JOIN 진료진행테이블 ON 애완동물테이블.PetID = 진료진행테이블.PetID) " & _
" ON 진료타입테이블.TreatID = 진료진행테이블.TreatID " & _
" GROUP BY 애완동물테이블.PetName;"
Case "월별매출"
sSQL = sSQL & "FORMAT(Month([WorkDate]),""00월"") AS 월별, Sum(진료타입테이블.[Price]) AS Total " & _
" FROM 진료타입테이블 INNER JOIN 진료진행테이블 ON 진료타입테이블.TreatID = 진료진행테이블.TreatID " & _
" GROUP BY Month([WorkDate]);"
End Select
Set oRst = UserForm2.getRecordset(sSQL)
If oRst Is Nothing Then Exit Sub
Application.DisplayAlerts = False
Worksheets(SHT_RPT).Delete
Application.DisplayAlerts = True
Set shtReport = Worksheets.Add
shtReport.Name = SHT_RPT
With shtReport
For iCol = 0 To oRst.Fields.Count - 1
.Range("A1").Offset(, iCol).Value = oRst.Fields(iCol).Name
Next
.Range("A2").CopyFromRecordset oRst
End With
End Sub
이렇게 하여 Access와 Excel을 같이 사용하여 VBA프로그래밍의
파워를 올려보는 시리즈는 마친다..
너무 장황하게 길게 전개했다..
하지만 엑셀에서만 사용하는 상태에서..
DB를 만들고, DB를 엑셀과 연결하는 다양한 것을 하다 보니 길어졌다
DB와 엑셀관계를 완전히 이해하고 활용하시려면 꼭 몇번을 보아서라도
실무에 필요한 DB를 만들어 보고, 구현해 보신다면 좋을 것이다
다음 더 흥미있는 시리즈를 준비하자..
***[LOG-IN]***