Eric Hill( 에릭힐 ), SAS의 JMP Principal Systems Developer
참고: 이것은 논문의 텍스트일 뿐이며 스크린샷은 포함되지 않았습니다. 전체를 보려면 첨부된 논문 버전을 참조하세요.
쿼리 빌더
SQL 데이터를 JMP로 가져오기 위한 새로운 JMP 12 도구
(JMP 12.1에 대해 업데이트됨)
Eric Hill, 소프트웨어 개발자, JMP
내용물
- 소개. 2
섹션 1: 연결 만들기. 3
섹션 2: 쿼리를 위한 테이블 선택. 6
섹션 3: 쿼리 작성. 11
열 추가. 13
쿼리 저장. 15
쿼리 실행. 15
질문에 답하기. 16
- 필터링. 19
프롬프트 필터. 23
- 견본 추출. 25
자신의 SQL 작성. 26
섹션 4: 백그라운드 쿼리 관리. 27
쿼리 상태 패널 28
Runninq 쿼리 창..28
섹션 5: 쿼리 공유. 28
섹션 6: JMP 스크립팅 언어(JSL)에서 쿼리 실행. 29
JMP 12.1의 새로운 기능은 무엇입니까?. 31
- 찾다. 31
관련 테이블 추가. 32
템플릿으로 열기. 34
계산된 열 수식 편집. 34
버그 수정. 35
- 결론. 35
소개
JMP는 광범위한 분석 도구를 손끝에 제공하지만, 필요한 데이터를 필요한 방식으로 JMP에 가져올 수 없다면 이러한 도구는 아무 소용이 없습니다. JMP는 파일 > 데이터베이스 > 테이블 열기를 통해 관계형 데이터베이스에서 JMP로 데이터를 가져오는 기능을 꽤 오랫동안 지원해 왔습니다. 테이블 열기는 지금까지는 괜찮았지만, 몇 가지 주목할 만한 제한 사항이 있습니다.
- 대부분 관계형 데이터베이스의 데이터는 정규화된 방식으로 저장되므로 필요한 모든 데이터를 얻으려면 조인이 필요하지만 오픈 테이블에서는 조인을 사용할 수 없습니다. 사용자가 직접 SQL을 작성해야 합니다.
- 오픈 테이블은 WHERE 절을 작성하기 위한 수식 편집기를 제공하지만, 예를 들어 범주형 변수의 고유 값을 표시하여 필터링하는 데 도움이 되지는 않습니다.
- 오픈 테이블은 생성된 JSL 스크립트의 연결 문자열에 비밀번호를 저장하기 때문에 다른 사람과 쿼리를 안전하게 공유하는 것이 어려울 수 있습니다.
- 오픈 테이블은 데이터를 가져온 후에 필요한 데이터 정리, 모델링 유형, 서식 지정 등의 사후 처리 단계에는 도움이 되지 않습니다.
Query Builder는 오픈 테이블 의 한계를 해결하기 위해 처음부터 설계되었으며, 동료와 안전하게 공유할 수 있는 프롬프트 대화형 필터링을 통해 다중 테이블 쿼리를 작성할 수 있도록 도와줍니다.
이 논문에서는 델스토어2 라는 무료로 이용 가능한 포스트그레스큐엘 샘플 데이터베이스와 함께 쿼리모델을 사용할 것입니다. PostgreSQL은 여기에서 무료로 다운로드할 수 있는 오픈소스 데이터베이스입니다.
http://www.postgresql.org/download/
델스토어2 샘플 데이터베이스는 여기에서 찾을 수 있습니다:
http://wiki.postgresql.org/wiki/Sample_Databases
델스토어2 샘플 데이터베이스는 다른 데이터베이스 플랫폼(MySQL, Oracle 및 SQL Server)에서도 사용할 수 있습니다.
섹션 1: 연결 만들기
메뉴에서 파일 > 데이터베이스 > 쿼리 빌더를 사용하여 쿼리빌더를 시작합니다.
JMP 스타터 의 첫 번째 페이지에서도 시작할 수 있습니다.
마지막으로 Windows에서는 홈 창 도구 모음에서 쿼리모델 도구를 찾을 수도 있습니다.
쿼리빌드를 실행하면 데이터베이스 연결 선택 대화 상자가 나타납니다.
The 데이터베이스 연결 선택 dialog box is a subset of the 오픈 테이블 window. 쿼리 빌더 and 오픈 테이블 use the same ODBC connections and in fact share connections, so any connections you make in either place will show up in both.
새 연결… 을 클릭하여 기존 DSN을 사용하여 데이터베이스에 연결하거나 새 DSN을 만듭니다. 필요한 자격 증명을 제공하라는 메시지가 표시됩니다.
연결 후 JMP는 연결에서 스키마를 검색하여 이것이 원하는 연결인지 확인하는 데 도움을 줍니다.
선택한 스키마에서 사용 가능한 테이블도 볼 수 있습니다. Next를 클릭하여 다음 단계로 넘어갑니다.
JMP 12.2의 새로운 기능: 스키마 - 테이블 패널에 있는 두 개의 검색 상자를 주목하세요. 이는 JMP 12.1 업데이트에서 새로 추가된 기능으로, 스키마 및 테이블 목록에서 입력하면서 필터링합니다. 여러분 중 많은 분들이 주어진 스키마에 수백 개의 스키마와 수백 개의 테이블을 가지고 있다는 것을 알고 있으므로, 새로운 검색 상자를 사용하면 목록을 좁혀 원하는 것을 찾을 수 있습니다.
섹션 2: 쿼리를 위한 테이블 선택
그림 1: 쿼리를 위한 테이블 선택
다음을 클릭하면 테이블 선택 대화 상자가 나타납니다. 테이블 선택 대화 상자에서 쿼리에 포함할 테이블을 선택합니다(위의 그림 1 참조). 사용 가능한 테이블 목록에서 쿼리에 포함할 테이블을 선택하고 오른쪽 위의 기본 및 보조 테이블 목록에 추가합니다.
- 쿼리의 기본 테이블은 일반적으로 다른 테이블에 일치하는 값이 있는지 여부와 관계없이 모든 행을 검색해야 하는 테이블입니다. 이는 데이터 웨어하우징 용어로 팩트 테이블 입니다. 쿼리에는 기본 테이블이 하나만 있을 수 있습니다.
- 보조 테이블은 일반적으로 조회 테이블입니다. 기본적으로 보조 테이블의 행은 기본 테이블의 열 값이 보조 테이블의 지정된 열 값과 일치할 때만 쿼리에 포함됩니다. 이는 데이터 웨어하우징 용어로 차원 테이블 입니다. 원하는 만큼 많은 보조 테이블을 쿼리에 추가할 수 있습니다.
오른쪽 아래의 패널은 선택한 테이블에 대한 정보를 보여줍니다. 열 탭은 테이블의 각 열에 대한 정보를 표시합니다. 여기에는 어떤 종류의 키인지 여부도 포함됩니다.
JMP 12.2의 새로운 기능:JMP 12.1의 새로운 기능인 사용 가능한 테이블 패널의 검색 상자를 참고하세요. 스키마에 많은 테이블이 포함되어 있는 경우 찾고 있는 테이블을 찾는 데 사용합니다.
테이블 스냅샷 패널은 데이터 스냅샷을 표시하여 테이블에 필요한 정보가 있는지 확인할 수 있습니다.
일반적으로 쿼리를 작성할 때는 답하고자 하는 질문이 하나 이상 떠오르고, 이러한 질문은 쿼리에 어떤 테이블을 포함해야 하는지에 대한 결정을 내립니다. 우리가 탐색하는 데이터는 가상의 DVD 대여 회사에서 가져온 것입니다. 내가 답하고자 하는 질문은 다음과 같습니다.
- 어떤 영화 장르가 가장 많은 수익을 올리고 있나요?
- 어떤 행위자들이 가장 많은 수익을 올리고 있나요?
대여 테이블에는 이 회사의 수명 동안의 각 DVD 대여 이벤트에 대한 레코드가 있으므로 이 쿼리의 기본 테이블이 됩니다. 열 패널에서 대여에 인벤토리 및 고객 테이블과 외래 키 관계가 있는 열이 있음을 알 수 있습니다.
그림 2: 외래 키
외래 키 관계(외래 키 제약 조건 이라고도 함)는 한 테이블의 열에 있는 값이 다른 테이블의 기본 키 열에서 발견되어야 함을 의미합니다. 그림 2 에서 렌탈 테이블의 인벤토리_아이디 열의 값은 List 테이블의 기본 키 열에 존재해야 하고, 렌탈 테이블의 고객 ID 열의 값은 customers 테이블의 기본 키 열에 존재해야 하며, rentals 테이블의 직원_아이디 열의 값은 직원 테이블의 기본 키 열에만 존재함을 알 수 있습니다.이 쿼리의 경우 대여에 참여한 직원의 고객보다는 대여한 영화에 대한 자세한 정보를 찾고 있으므로 List 테이블을 보조 테이블로 쿼리에 추가하겠습니다.
목록 테이블의 열 패널을 살펴보면 필름 테이블과의 외래 키 관계를 더욱 확인할 수 있습니다.
그림 3: 재고 테이블 세부 정보
그래서 movie 테이블도 쿼리에 들어갑니다. movie 테이블의 세부 정보를 보면 임대료 열이 보이는데, 수익을 계산하는 데 필요한 것이어야 하지만, 영화 카테고리나 영화에 나오는 배우에 대한 내용은 보이지 않습니다.
그림 4: 영화 표의 열
하지만 사용 가능한 테이블 목록을 보면 영화배우 테이블과 영화 카테고리 테이블이 있는데, 이는 영화의 ID를 배우와 카테고리에 매핑하는 것으로 보이며, 배우 와 카테고리에 대한 정보가 더 많은 actor 및 범주 테이블도 있습니다. 그래서 저는 쿼리에 이러한 테이블도 원할 것입니다. 하지만 그 4개의 테이블을 그냥 추가하면 뭔가 잘못된 것 같습니다.
그림 5: 자동 조인 실패
중앙에 "X"가 있는 "정지 신호" 아이콘은 JMP가 두 테이블을 자동으로 조인할 수 없다는 것을 나타내므로 조인을 수동으로 설정해야 합니다. JMP의 자동 조인 기능이 자주 필요한 작업을 수행하지 않는 경우 쿼리를 위한 테이블 선택 옆에 있는 빨간색 삼각형에서 끌 수 있습니다.
public.film_actor(t14)를 선택하고 편집 참여 버튼( )을 클릭하면 조건 수정 대화 상자가 나타나서 조인을 구성할 수 있습니다. 처음에는 대화 상자가 다음과 같습니다.
그림 6: 조인 조건 편집
아직 조인 조건이 없으므로 film_actor.film_id를 인벤토리.필름_아이디 에 조인하고 싶습니다. 이를 위해 왼쪽 열 목록에서 목록 테이블을 선택하고 두 목록에서 film_id를 선택하면 됩니다.
그림 7: 조인 조건 편집
마찬가지로 공공.영화_카테고리 에 대한 조인 조건을 인벤토리.필름_아이디 = 필름_카테고리.필름_아이디 로 설정할 수 있습니다. 그렇게 한 후에는 상황이 더 좋아 보입니다.
그림 8: 테이블 간 조인이 유효합니다.
모든 보조 테이블은 왼쪽 외부 조인 벤 다이어그램 아이콘을 보여줍니다. 방금 영화배우 와 목록 사이에 만든 조인을 예로 들면, 왼쪽 외부 조인은 영화배우 테이블에 일치하는 것이 있든 없든 목록 테이블(이 경우 왼쪽 테이블)의 모든 행이 쿼리에 포함되지만, 인벤토리 테이블의 행과 일치하는 film_actor 테이블의 행만 포함된다는 것을 의미합니다.
그런데 테이블 이름 뒤에 있는 괄호 안의 티1 , t2 , t3 등은 JMP가 쿼리에 테이블을 추가할 때 테이블에 지정하는 테이블 별칭입니다. 테이블 별칭을 사용하면 올바른 SQL을 더 쉽게 생성할 수 있으며 생성된 SQL을 더 읽기 쉽게 만들 수 있습니다. 주요한 또는 반성 테이블 목록에서 테이블을 마우스 오른쪽 버튼으로 클릭하고 별칭 변경을 선택하여 테이블의 테이블 별칭을 변경할 수 있습니다. 테이블 별칭에는 공백이나 특수 문자가 포함될 수 없습니다.
왼쪽 외부 조인 외에 다른 것이 필요한 경우, 편집 협력 대화 상자의 확인란을 사용하여 조인 유형을 변경할 수 있습니다. 조인 유형과 벤 다이어그램 아이콘이 확인란의 상태를 반영하도록 변경됩니다.
그림 9: 조인 유형 변경
조인이 올바르게 설정되었는지 확인하려면 미리보기 참여 버튼을 클릭하면 됩니다. 그러면 쿼리가 실행되고 처음 몇 백 행의 데이터 스냅샷이 표시되어 모든 것이 제대로 보이는지 확인할 수 있습니다.
이제 쿼리에 필요한 모든 테이블이 있으므로 '쿼리 작성'을 클릭하여 '쿼리모델 '을 불러올 수 있습니다. 여기서 쿼리에 포함할 열, 사용할 필터 기준, 결과 데이터를 정렬하는 방법 등을 지정할 수 있습니다.
섹션 3: 쿼리 작성
이 쿼리에 대한 쿼리 창이 처음 나타나면 다음과 같습니다.
- 쿼리 이름 상자에는 쿼리 이름이 들어 있으며, 기본적으로 기본 테이블 이름이 지정됩니다. 쿼리 이름은 쿼리를 실행하여 생성된 JMP 데이터 테이블에 지정된 이름이며, 쿼리를 파일에 저장할 때의 기본 파일 이름이기도 합니다. 데이터 소스는 쿼리가 사용하는 데이터 소스의 이름을 보여줍니다.
- 테이블 상자는 현재 쿼리에 있는 테이블을 보여줍니다. 테이블을 추가하거나 제거하거나 조인을 변경하려면 변경… 버튼을 클릭합니다.
- 사용 가능한 열 목록에는 쿼리의 모든 테이블의 모든 열이 표시됩니다. 이 목록에서 열을 선택하여 포함된 열 , 필터 또는 정렬 기준 패널로 끌어다 놓습니다. 또는 원하는 경우 마우스 오른쪽 버튼을 클릭하여 동일한 작업을 수행할 수 있습니다.
JMP 12.1의 새로운 기능: 사용 가능한 열 패널에 이제 긴 목록에서 특정 열을 찾아야 하는 경우 찾을다 상자가 있습니다. 다른 검색 상자와 마찬가지로 입력하는 동안 필터링합니다. - 열 패널은 쿼리에 포함된 모든 열의 그리드를 보여줍니다. 열 그리드를 사용하여 JMP로 가져온 후 열의 데이터가 가져야 하는 형식 및 모델링 유형과 같은 항목을 설정합니다. JMP에 데이터를 가져오기 전에 요약하려는 경우 열에 대한 그룹화 와 함께 집계를 지정할 수도 있습니다.
샘플링을 지원하는 데이터베이스의 경우, 이 패널에는 서버에서 무작위 데이터 샘플을 생성하기 위한 샘플 탭도 있습니다. - 이 영역의 쿼리 미리 보기 탭은 쿼리를 실행할 때 반환되는 데이터의 스냅샷을 표시하도록 자동으로 업데이트됩니다. SQL 탭은 현재 선택 사항을 기반으로 QueryBuild가 생성하는 SQL을 표시합니다. 쿼리 후 스크립트 탭을 사용하면 쿼리가 완료된 후 실행될 JSL 스크립트를 지정할 수 있습니다. 쿼리 상태 탭은 백그라운드에서 실행 중인 쿼리의 상태를 표시하고 이를 취소할 수 있습니다.
- 필터링 패널을 사용하면 데이터 필터링 기준(WHERE 절 생성)을 지정하고, 쿼리를 실행할 때마다 실행되는 프롬프트로 필터를 전환할 수 있습니다.
- 주문 기준 패널을 사용하면 반환된 행을 정렬하는 데 사용할 열(ORDER BY 절)을 지정할 수 있습니다.
열 추가
모든 쿼리에는 몇 개의 열이 필요합니다. 다음 방법 중 하나를 사용하여 쿼리에 열을 추가합니다.
- 쿼리의 모든 테이블에서 사용 가능한 모든 열을 추가하려면 모두 추가 버튼을 클릭하세요.
- 사용 가능한 열 목록에서 특정 열을 선택하고 추가를 클릭하거나 해당 열을 끌어다 놓습니다.
- Double-click on a column in the 사용 가능한 열 list.
이 쿼리에서는 영화 테이블에서 필름_아이디 , 제목 , 임대료를 추가하고, 범주 테이블에서 카테고리_아이디 와 name 을 추가하고, 배우 테이블에서 actor_id , 이름 , last_name 을 추가합니다.
그림 10: 처음에 열을 추가한 후
JMP 이름, 모델링 유형 및 형식 열 을 사용하면 데이터를 JMP로 가져온 후 각 열의 이름, 모델링 유형 및 형식이 어떻게 될지 제어할 수 있습니다.
그림 11: 열 세부 정보 조정 후
이 분석의 목적을 위해, 나는 또한 배우 이름이 별도의 이름 과 성 열로 분할되는 대신 단일 열에 연결되는 것을 선호합니다. 이를 위해 사용 가능한 열 패널의 빨간색 삼각형 메뉴에서 계산된 열 추가를 선택하여 계산된 열을 추가할 수 있습니다.
이렇게 하면 계산1 이라는 열이 사용 가능한 열 목록에 추가되지만, 이 열을 마우스 오른쪽 버튼으로 클릭하고 열 이름 바꾸기를 사용하여 이름을 배우 이름 으로 바꾸고 쿼리에 추가한 다음 이름 과 성을 제거할 수 있습니다. 이렇게 한 후 쿼리의 미리 보기는 다음과 같습니다.
그림 12: 계산된 열이 있는 쿼리 미리보기
(해당 Actor Name 값은 조금 이상해 보일 수 있지만, 사실 이는 데이터베이스의 올바른 이름입니다. Actor 이름은 성과 이름을 무작위로 짝지어 생성한 것으로 보입니다.)
쿼리 저장
이 시점에서 이 쿼리에 투자한 시간이 충분해서 작업을 잃지 않도록 저장하고 싶습니다. 쿼리모델 창의 오른쪽 하단에 있는 저장 버튼을 클릭하기만 하면 쿼리가 저장됩니다. 쿼리 모델 상자의 텍스트가 기본값으로 제공되고 파일 이름을 입력하라는 메시지가 표시됩니다.쿼리 모델 이름을 영화 카테고리 배우 로 지정하겠습니다. 쿼리모델 창의 왼쪽 하단에 쿼리가 저장되었다는 확인 메시지가 표시됩니다.
Saving a query creates a file with a .jmp쿼리 extension. Back on the 쿼리 빌더 window, you’ll notice that the 쿼리 이름 has been changed to match the file name, so it is now 영화 카테고리 배우.
열어 놓은 쿼리와 비슷한 다른 쿼리를 만들어야 하지만 원본을 보존하려는 경우 '다른 이름 으로 저장...' 버튼을 사용하여 다른 이름으로 사본을 저장할 수 있습니다.
쿼리 실행
이 시점에서 쿼리를 실행하여 데이터베이스의 모든 행을 포함하는 JMP 데이터 테이블을 만들 수도 있습니다. 쿼리모델 창의 하단에 있는 쿼리 실행 버튼을 클릭하여 쿼리를 실행합니다.기본적으로 JMP는 백그라운드에서 쿼리를 실행하므로 쿼리가 실행되는 동안에도 작업을 계속할 수 있습니다.창의 하단 가운데 섹션에 있는 쿼리 상태 탭을 사용하여 이 쿼리모델 창에서 아직 진행 중인 쿼리의 상태를 확인하거나 보기 > 실행 중인 쿼리를 사용하여 현재 백그라운드에서 실행 중인 모든 쿼리의 상태를 확인합니다.이 특정 쿼리는 1~2초 밖에 걸리지 않으며 88,020개의 행을 반환합니다.
그림 13: JMP 데이터 테이블의 쿼리 결과
결과 JMP 데이터 테이블에는 3개의 스크립트와 SQL 테이블 변수가 정의되어 있습니다.
질문에 답하기
이 시점에서 저는 원래 제가 답을 찾고 있던 질문에 거의 대답할 수 있게 되었지만, 결정을 내려야 합니다. 데이터베이스에서 결과를 요약할 것인지, 아니면 원시 데이터를 JMP로 가져와서 JMP에서 작업을 할 것인지요? 원시 데이터가 메모리에 맞을 만큼 크지 않고 가져오는 데 엄청나게 오랜 시간이 걸리지 않는다면, 원시 데이터를 JMP로 가져와서 거기에서 분석을 하는 것이 여러 가지 이점이 있습니다. JMP는 관계형 데이터베이스보다 데이터를 분석하는 능력이 훨씬 뛰어납니다.
현재 작업의 경우, 카테고리 이름 및 배우 이름 에서 분포 플랫폼을 사용하고 Rental Rate 를 빈도 변수로 사용하는 간단한 문제입니다. 결과 히스토그램을 오름차순으로 계산 으로 정렬하면 두 가지 질문에 모두 답하는 결과를 얻습니다.
그림 14: 쿼리 결과에 대한 배포 플랫폼 사용
그러나 모든 원시 데이터를 가져오는 것이 크기나 경과 시간 면에서 금지되어 있다면 대안으로 𝐬 열 패널의 집계 열을 사용하여 데이터베이스에서 데이터를 요약할 수 있습니다. 그러나 그렇게 하려면 두 질문에 하나씩 답해야 합니다.
To answer the question of 어떤 영화 장르가 가장 많은 수익을 올리고 있나요?, I can remove the columns having to do with actor information from the query and ask the database to sum 임대료 grouping by 카테고리 이름, and then I can order the result by the summed 임대료. Here are the steps:
- 분할 열을 클릭하고 Sum 을 선택 합니다.
지정된 집계가 없는 모든 변수에 대해 그룹화 기준이 체크된다는 점에 유의하세요. 쿼리의 열이 집계를 지정하면, 집계를 지정 하지 않는 쿼리의 모든 열은 그룹화 기준 열이어야 합니다. - Right-click on the 변수 이름 column for 임대료 and select 주문 기준.
- 주문 기준 패널에서 SUM(t4.rental_rate)를 선택하고 아래쪽을 가리키는 화살표 버튼( 내림차순 )을 클릭합니다.
결과는 다음과 같습니다.
Similarly, taking the category columns back out and putting the actor columns back in, I can answer the question of 어떤 행위자들이 가장 많은 수익을 올리고 있나요?:
필터링
쿼리가 모든 원시 데이터를 검색하고, 수익 질문에 답하기 위해 Duplicate 플랫폼을 사용하던 상태로 잠시 돌아가 보겠습니다. JMP에서 분석을 수행할 때 사용할 수 있는 한 가지 옵션은 JMP 분석에 대한 스크립트를 요청할 수 있다는 것입니다.
I can then paste that script into the 쿼리 후 스크립트 panel of 쿼리 빌더:
그림 15: 쿼리 후 JSL 스크립트 추가
이제 해당 스크립트는 제가 쿼리를 실행할 때마다 실행될 것입니다.
이제 제 질문을 조금 더 구체화하고 싶습니다. 데이터베이스의 모든 대여에 대한 카테고리와 배우 수익을 살펴보는 대신, 영화를 대여한 국가, 대여한 매장 또는 특정 날짜 범위에 따라 살펴보고 싶을 수도 있습니다.
lease 테이블에는 임대_날짜 와 customers ID 필드가 있으므로 필요한 것을 거의 다 얻었습니다. 앞서 customers ID가 customers 테이블에 대한 외래 키 참조라는 것을 보았습니다. customers 테이블에서 고객이 있는 국가와 DVD를 대여한 매장을 가져올 수 있습니다.
Query Builder의 테이블 패널에서 변화… 버튼을 클릭하면 돌아가서 쿼리에 테이블을 더 추가할 수 있습니다. 국가 정보를 얻으려면 customers , 주소 , 도시 , 국가 테이블을 쿼리에 추가해야 합니다. 다행히도 이 데이터베이스의 관리자가 설정한 주요 관계 덕분에 모든 테이블이 자동으로 성공적으로 조인됩니다.
필요한 추가 테이블을 추가한 후, Query Builder로 돌아가서, 필요한 열을 쿼리에 추가할 수 있습니다: 임대날짜 , 매장_아이디 , 그리고 country . 또한 친숙한 JMP 이름을 설정하고 매장_아이디 의 모델링 유형을 수정했습니다:
그림 16: 대여 날짜, 매장 ID 및 국가 열 추가 후
먼저 고객의 국가를 필터링해 보겠습니다.投点 열 패널에서 t7.국가 변수를 마우스 오른쪽 버튼으로 클릭하고 필터 기준을 선택할 수 있습니다. 그러면 필터 패널에 국가 필터가 나타납니다.
그림 17: 국가 필터
이 시점에서 국가 목록을 아래로 스크롤하여 관심 있는 국가를 선택한 다음 쿼리를 실행하면 해당 국가의 행만 포함됩니다.
Similarly, I can add filters for 매장_아이디 and 임대_날짜, after which the Filters panel looks like this:
임대날짜 필터를 추가하면 연속 변수이므로 기본적으로 단순 비교 필터가 생성됩니다. 대신 범위 필터가 필요합니다. t1.대여_날짜 옆에 있는 빨간색 삼각형 메뉴를 사용하여 필터 유형을 범위 로 변경할 수 있습니다.
Now the filter for t1.대여_날짜 looks like:
날짜 범위를 지정할 수 있습니다. 따라서 2005년 하반기의 모든 렌털을 찾으려면 다음과 같이 할 수 있습니다.
그림 18: 날짜 범위 필터
쿼리폼 의 모든 필터 유형에는 효과적으로 비활성화할 수 있는 방법이 있어, 모든 필터가 주어진 쿼리에 대해 활성화되지 않더라도 쿼리에 여러 필터를 추가할 수 있습니다. 값 목록을 표시하는 범주형 필터의 경우 맨 위에 있는 (모두) 항목을 선택하면 필터가 비활성화됩니다. 숫자 값을 요청하는 필터의 경우 값을 누락 (.) 으로 설정하면 필터(또는 필터의 일부)가 비활성화됩니다. 따라서 2006년 1월 1일부터의 모든 임대료를 원하는 경우 다음과 같이 할 수 있습니다.
이제 내 쿼리에 대한 필터 패널은 아래 그림 19 와 같습니다.
| 그림 19: 누락된 필터를 사용하여 비활성화 | 그림 20: 두 개의 OR 그룹이 있는 필터 |
그림 19 에 표시된 필터 선택 사항을 사용하여 지금 쿼리를 실행하면 고객의 국가가 프랑스 또는 독일이고 DVD가 2006년 1월 1일 이후에 매장 2에서 대여된 행이 반환됩니다.기본적으로 여러 필터는 AND로 연결되므로 행이 모든 필터와 일치해야 결과에 나타납니다.OR 필터가 필요한 경우 또는 버튼을 클릭하면 다른 필터 그룹이 생성되고 두 필터 그룹이 OR로 연결됩니다.예를 들어 위의 그림 20 에 표시된 필터는 카테고리가 드라마 이거나 영화 제목에 "버킷"이 포함된 대여 항목을 반환합니다.
프롬프트 필터
쿼리모델 에서 필터 기준을 지정할 수 있는 것은 좋지만, 전체 쿼리모델 사용자 인터페이스가 실제로 제공되지 않아도 다른 사람이 사용할 쿼리를 만들고 싶을 수도 있습니다. 그러나 쿼리를 실행할 때마다 다른 필터 기준을 입력할 수 있게 하려는 경우가 있습니다. 이를 달성하는 가장 간단한 방법은 filter 옆에 있는 빨간색 삼각형 메뉴에서 실행 시 모든 프롬프트를 선택하는 것입니다.
그러면 쿼리의 모든 필터가 프롬프트 필터로 바뀝니다. 일부 필터만 프롬프트하고 다른 필터는 프롬프트하지 않으려면 빨간색 삼각형 메뉴를 사용하여 각 필터에 대한 프롬프트를 켜고 끌 수 있습니다.
All Prompt On Run을 설정한 후 이 쿼리를 실행하면 원하는 대로 필터 값을 설정하라는 메시지가 표시됩니다.
프롬프트의 기본 설정은 쿼리가 실행되기 전에 쿼리모델 에서 선택된 값(또는 쿼리가 파일에서 직접 실행된 경우 저장된 쿼리 파일에서 선택된 값)이 됩니다.
프롬프트에서 사용되는 레이블을 사용자 정의할 수도 있습니다. 예를 들어, " 국가: " 대신 " 고객 국가: "라고 프롬프트를 표시하고 싶다고 가정합니다. Query Builder로 돌아가서 t7.국가 필터의 빨간색 삼각형 메뉴를 사용하여 프롬프트 구성을 선택합니다. 그러면 프롬프트의 레이블을 변경하고 프롬프트의 범주 수준을 제공하는 테이블도 변경할 수 있는 대화 상자가 나타납니다.
견본 추출
데이터베이스가 임의 샘플링을 지원하는 경우 쿼리빌더 의 상단 중앙 섹션에 있는 Promo 탭에 데이터베이스가 지원하는 샘플링 옵션이 표시됩니다. 이 논문의 예제에서는 지정된 수의 행에 대한 단순 임의 샘플링을 지원하는 PostgreSQL을 사용했습니다.
SQL Server와 Oracle은 다양한 샘플링 옵션을 지원하므로 검색 탭에는 다양한 옵션이 반영됩니다.
| 그림 21: SQL Server 샘플링 옵션 | 그림 22: Oracle 샘플링 옵션 |
샘플링은 데이터가 JMP에 다운로드되기 전에 데이터베이스에서 수행되므로, 이는 엄청나게 많은 수의 행을 검색하는 쿼리에서 검색되는 행 수를 제한하는 좋은 방법입니다.
자신의 SQL 작성
우리는 쿼리모델이 현재 또는 미래에 가질 수 있는 많은 기능만큼이나, 쿼리모델이 직접 지원하지 않는 작업을 수행하는 쿼리가 필요할 때가 있다는 것을 알고 있습니다. 또한, 여러분은 이미 JMP에서 수행해야 하는 쿼리를 정확히 수행하는 SQL을 작성하고 꼼꼼히 디버깅했을 수도 있습니다. 쿼리모델 에는 쿼리에 대한 고유한 SQL을 제공하기 위한 두 가지 옵션이 있습니다.
- 사용자 지정 필터 표현식 - 쿼리빌더가 테이블을 선택하고 포함할 열을 지정하는 데 필요한 작업을 수행하지만 필터의 일부로 사용자 지정 SQL 표현식만 포함하려는 경우 필터 빨간색 삼각형 메뉴에서 사용자 지정 표현식 추가를 선택할 수 있습니다.
| 그림 23: 사용자 정의 필터 표현식 추가 | 그림 24: 결과 필터 |
쿼리에 유효한 SQL을 사용자 지정 표현식 상자에 입력하세요.
사용자 정의 표현식 필터가 프롬프트 필터로 설정된 경우 사용자 정의 표현식이 필터 프롬프트 대화 상자에 나타나고 편집할 수 있습니다.
- 전체 쿼리를 사용자 지정 SQL 쿼리 로 변환 – 전체 쿼리에 대한 자체 SQL을 작성하려는 경우 QueryBuilder를 실행하고 데이터 연결을 지정한 후 QueryBuilder 의 왼쪽 상단에 있는 쿼리 이름 필드 옆에 있는 빨간색 삼각형 메뉴를 사용하여 사용자 지정 SQL로 변환을 선택할 수 있습니다.
그림 25: 사용자 정의 SQL로 변환
쿼리폼 창은 사용자 지정 SQL 모드로 전환되어, 사용자가 직접 SQL을 입력할 수 있는 넓은 영역을 제공합니다. 대화형으로 쿼리를 빌드하기 시작한 경우, 사용자 지정 SQL 상자는 지금까지 선택한 내용을 기반으로 생성된 SQL로 초기화됩니다(아래 그림 26 참조).
그림 26: 사용자 정의 SQL 쿼리
필요에 따라 SQL을 편집하거나 다른 소스에서 SQL을 붙여넣은 다음 업데이트 버튼을 클릭하여 쿼리 미리보기 를 업데이트할 수 있습니다. 몇 가지 주의 사항:
- 사용자 정의 SQL 쿼리로는 프롬프트 필터링을 수행할 수 없습니다.
- 쿼리를 사용자 지정 SQL로 변환하고 SQL을 편집한 후 쿼리를 대화형으로 되돌리면 SQL에 적용한 모든 사용자 지정이 손실됩니다. 쿼리 빌더는 SQL을 해석하려고 시도하지 않습니다.
섹션 4: 백그라운드 쿼리 관리
쿼리모델은 기본적으로 백그라운드에서 쿼리를 실행하고, JMP 스크립팅 언어(JSL)에서도 백그라운드에서 쿼리를 실행할 수 있습니다( 섹션 6 참조). JMP에서는 현재 실행 중인 백그라운드 쿼리 목록을 보고 필요한 경우 실행 중인 쿼리를 중지하는 두 가지 방법을 제공합니다.
쿼리 상태 패널
Query Builder의 하단 중간 섹션에는 Query 상태 탭이 있습니다. 해당 Query Builder 창에서 시작된 모든 백그라운드 쿼리는 해당 탭에 나타납니다.
실행 중인 쿼리를 중지하려면 목록에서 선택하고 중지 버튼을 클릭하기만 하면 됩니다. 일부 행이 검색된 경우 JMP는 이미 검색된 행이 포함된 데이터 테이블을 엽니다. 데이터 테이블의 이름은 결과가 부분적임을 반영합니다.
Runninq 쿼리 창
홈 창이나 메뉴가 포함된 다른 JMP 창의 보기 메뉴에서 실행 중인 쿼리를 선택합니다. 그러면 쿼리빌더 의 쿼리 상태 탭과 같은 창이 열리지만 실행 중인 쿼리 창에는 호출 방법(쿼리 빌더 창 또는 JMP 스크립팅 언어)에 관계없이 현재 백그라운드에서 실행 중인 모든 쿼리가 포함됩니다.
여기서도 쿼리를 선택하고 '중지'를 클릭하면 실행이 중지되고 부분적인 결과가 열립니다.
섹션 5: 쿼리 공유
서론에서 오픈 테이블은 데이터베이스 비밀번호가 연결 문자열에 포함되어 있기 때문에 공유하기 어려운 스크립트를 만들었다고 언급했습니다. 쿼리모델은 다르게 작동합니다. 비밀번호가 필요한 연결에 대한 쿼리에 대한 .jmp쿼리 파일을 저장하고 해당 파일을 텍스트 편집기에서 열면 다음과 같은 내용이 표시됩니다.
사용자 이름( 사용자 ID )이 연결 문자열에 있는 것을 볼 수 있지만 PWD=%_PWD_%가 표시됩니다. "%_PWD_%"는 암호가 아닙니다. Query Builder에서 넣은 플레이스홀더입니다. Query Builder가 해당 플레이스홀더를 보면 원래 암호가 필요했음을 알고 있으므로 사용자가 포스트그레스 dvd렌탈 DSN에 아직 연결하지 않은 상태에서 이 쿼리를 열면 Query Builder는 사용자에게 암호를 묻습니다.
DSN 자체는 어떨까요? 이 .jmpquery 파일을 Postgres dvdrental 이라는 DSN이 머신에 정의되어 있지 않은 JMP 사용자에게 보내면 어떨까요? 오픈 테이블은 이런 시나리오에서 진행할 수 없지만 쿼리모델은 사용자에게 이 상황을 알리고 다른 DSN(아마도 같은 데이터베이스에 연결)에 연결하거나 새 DSN을 만들 수 있는 기회를 제공합니다.
여러 사용자가 각자의 컴퓨터에서 서로 다른 DSN을 정의할 가능성 때문에 일부 조직에서는 DSN 없는 연결 로 전환했습니다. 쿼리모델은 현재 DSN 기반 연결 문자열을 DSN 없는 연결 문자열로 변환할 수 없지만, 쿼리의 스크립트에서 연결 문자열을 변경하여 DSN을 지정하는 대신 운전사 와 SERVER를 지정하면 쿼리모델은 생성하는 모든 스크립트나 .jmpquery 파일에 이 DSN 없는 연결을 전파합니다.
섹션 6: JMP 스크립팅 언어(JSL)에서 쿼리 실행
Query Builder에서 작성한 쿼리는 JMP Scripting Language(JSL) 스크립트에서도 실행할 수 있습니다. 가장 좋은 옵션은 쿼리를 .jmp쿼리 파일로 저장하고 JSL에서 열어서 실행하는 것입니다. 예를 들어, c:\사용자\공개\임시 폴더에 pg_rentals.jmp쿼리 라는 이름의 쿼리가 저장되어 있다고 가정해 보겠습니다. JSL에서 해당 쿼리 파일로 할 수 있는 몇 가지 작업은 다음과 같습니다.
- Query Builder에서 .jmpquery 파일을 엽니다.
열려 있는( "c:\users\public\temp\pg_rentals.jmp쿼리" );
질문= 열려 있는( "c:\users\public\temp\pg_rentals.jmp쿼리",사적인 );
- Open the query in 쿼리 빌더 for editing:
질문<< 수정하다;
질문<< 달리다();
- 포그라운드에서 쿼리를 실행합니다. 쿼리가 완료되면 결과 데이터 테이블이 JMP에서 열립니다. dtResult는 결과 데이터 테이블에 대한 참조가 됩니다.
결과=질문<< 전경 실행();
- 포그라운드에서 쿼리를 실행하지만 결과 데이터 테이블을 JMP에서 열지 말고 비공개로 유지하세요. dtResult는 결과 데이터 테이블에 대한 참조가 됩니다.
결과=질문<< 전경 실행(사적인 );
- 백그라운드에서 쿼리를 실행합니다. 쿼리가 완료되면 결과 데이터 테이블이 JMP에서 열립니다. Run Background는 쿼리가 단순히 백그라운드에서 실행되기 때문에 결과 데이터 테이블에 대한 참조를 반환할 수 없습니다. 이 예제 다음의 예제는 백그라운드에서 쿼리를 실행할 때 결과 데이터 테이블에 대한 참조를 가져오는 방법을 보여줍니다.
질문<< 실행 배경();
- 백그라운드에서 쿼리를 실행합니다. 쿼리가 성공하면 결과 데이터 테이블에서 Distribution 플랫폼을 호출합니다( queryResult는 결과 데이터 테이블을 참조하는 실행 완료 스크립트 내의 특수 JSL 변수입니다). 사용자가 쿼리를 취소하거나 오류가 있는 경우 JMP Log에 메시지를 씁니다.
질문<< 실행 배경(
실행 완료 (
쿼리 결과<< 분포( 명목 분포( 열( :제목 ) ) );
),
실행 취소됨 (쓰다( "\!N쿼리가 취소되었습니다!" ) ),
오류 발생 시 (쓰다( "\!N쿼리 실행 오류!" ); )
);
- 백그라운드에서 쿼리를 실행합니다. 쿼리가 성공적으로 완료되면 MyFunc 함수를 호출합니다. 쿼리 결과 데이터 테이블은 항상 함수의 첫 번째 매개변수로 전달됩니다.
::마이펑크= 기능({ 일 },{ 기본 로컬 },
쓰다( "\!n대여 횟수: ", 노우스() )이다;
디티<< 분포( 명목 분포( 열( :제목 ) ) );
);
질문<< 실행 배경(실행 완료 (::내기능 ) );
JSL 스크립트에서 별도의 .jmpquery 파일에서 쿼리를 여는 것이 만족스럽지 않은 경우 쿼리 빌더 창의 왼쪽 상단에 있는 빨간색 삼각형 메뉴에서 쿼리 스크립트 가능 개체를 만드는 원시 스크립트를 가져올 수 있습니다.
예를 들어, 간단한 쿼리에 대해 스크립트 창에 실행 스크립트 저장을 호출하면 다음과 같은 결과가 나옵니다.
새로운 SQL 쿼리는 .jmpquery 파일을 열 때 열려 있는 과 마찬가지로 SQL 쿼리 스크립트 가능 개체를 반환하며 모든 동일한 방식으로 사용할 수 있습니다.
JMP 12.1의 새로운 기능은 무엇입니까?
찾다
우리가 대화한 많은 사람들은 데이터베이스에 수백 개의 스키마, 테이블, 열에 엄청난 양의 데이터를 가지고 있습니다. JMP 12.1 버전의 Query Builder에서 스키마, 테이블, 열 목록에 찾다 상자를 추가하여 원하는 것을 빠르게 찾을 수 있도록 했습니다.
이러한 모든 찾기 상자는 입력하는 동안 업데이트되며, 정규 표현식을 사용하여 검색을 구체화할 수 있습니다.
관련 테이블 추가
쿼리를 위한 테이블 선택 대화 상자에서 쿼리에 추가한 테이블에 다른 테이블에 대한 외래 키 참조가 있는 경우, 관련 테이블 추가 단추를 클릭하여 모든 관련 테이블을 쿼리에 추가할 수 있습니다.
템플릿으로 열기
자주, .jmpquery 파일에 빌드하여 저장한 다른 쿼리와 매우 유사한 쿼리를 빌드해야 합니다. 언제든지 이전에 저장된 쿼리를 열고 변경한 다음 다른 이름으로 저장을 사용하여 다른 .jmpquery 파일에 저장할 수 있지만 실수로 구하다를 실수로 눌렀다면 원래 쿼리가 덮어쓰여집니다. 템플릿으로 열기 기능은 이러한 시나리오를 방지하기 위해 존재합니다. 템플릿으로 열기는 Mac과 Windows 모두에서 .jmpquery 파일의 홈 창 컨텍스트 메뉴에서 사용할 수 있습니다.
.jmpquery를 템플릿으로 열면 실제로 디스크에 있는 .jmpquery 파일과 관련이 없는 원본 쿼리의 사본을 여는 것입니다. 쿼리를 템플릿으로 열고 변경한 후 실수로 구하다 버튼을 눌렀다면 쿼리를 저장할 파일을 제공하라는 메시지가 표시됩니다.
JMP 스크립팅 언어(JSL)에서 템플릿으로 .jmpquery 파일을 열 수도 있습니다.
열려 있는( "C:\Users\Public\Temp\rentals.jmpquery",템플릿으로 );
계산된 열 수식 편집
JMP 12.0에서는 데이터베이스 서버에서 계산되고 결과 값이 쿼리에 반환되는 계산된 열을 쿼리에 추가할 수 있었습니다. 그러나 이전에 만든 계산된 열의 수식을 편집해야 하는 경우 그럴 수 없었습니다. 대신 계산된 열을 삭제하고 새 열을 추가해야 했는데, 복잡한 수식을 다시 만들어야 할 수도 있었습니다. JMP 12.1에서는 이제 계산된 열을 편집할 수 있는 옵션이 있습니다. 사용 가능한 열 에서 열을 마우스 오른쪽 버튼으로 클릭하고 수식 편집을 선택하기만 하면 됩니다.
버그 수정
예상하셨겠지만, JMP 12.1의 Query Builder에서 여러 버그를 수정했습니다. 그중 일부는 고객이 보고한 것입니다. Query Builder를 사용하는 데 문제가 발생하면 SAS 기술 지원을 통해 문제를 보고해 주시면 해결해 드리겠습니다.
결론
쿼리빌드 는 JMP 12(JMP 12.1에서 업데이트됨)의 새로운 기능으로, 관계형 데이터베이스에 대한 고도로 사용자 정의된 다중 테이블 SQL 쿼리를 빌드하는 데 도움이 됩니다. 이 기능의 목표는 이전보다 더 빠르게 분석에 적합한 형태로 데이터를 JMP에 가져오고 쿼리를 안전하게 공유하는 것을 더 쉽게 만드는 것입니다. 여러분이 직접 사용해 보고 피드백을 보내주시기 바랍니다(에릭힐@jmp.com) 무엇이 당신에게 잘 맞는지, 무엇이 맞지 않는지에 대해 알아보세요.
