데이터베이스 질문과 답변

쿼리문이 떠오르질 않아서 문의 드립니다. 0 6 1,904

by 김정완 [SQL Query] #join #sql #query [2022.11.28 21:13:09]

안녕하세요, 저는 작은 회사에서 DB를 맡고 운영하고 있습니다.

이번에 질문을 드리게 된 것은, 도저히 쿼리문이 떠오르지가 않아서 이렇게 여쭙게 되었습니다.

현재 아래와 같이 데이터를 모두 인입하여 넣고 엑셀 파일에 맞는 sql을 짜고 있습니다.

결국 출발지 x, y 좌표가 일정 구역인 곳에서 도착지 x,y 좌표가 일정 구역인 곳을 찾는 방법의 연속입니다.

 

출발 시간 / 도착 시간 / 여행 시간 / 출발지좌표 x / 출발지 좌표 y / 도착지 좌표 x / 도착지 좌표 y / odtype은 무시 / age도 무시 / popu는 인구합

oritime    destime    triptime    orix    oriy    desx    dexy    odtype    age    popu
21:30    22:30    60    1157000    1732500    1158000    1732000    HH    0    40
08:00    08:00    0    1164000    1724500    1165500    1725000    WE    0    40
09:00    11:00    120    967000    1708000    1148500    1730000    WE    0    40
12:30    13:00    30    1164000    1727000    1165000    1726500    HH    0    60
14:30    15:30    60    1169000    1731500    1164500    1730500    EH    0    50
17:30    17:30    0    1159000    1730000    1158500    1729500    EE    0    40
23:30    23:30    0    1168000    1732500    1167500    1737500    EH    0    40
19:30    19:30    0    1174500    1725000    1175000    1725500    WW    0    30
19:30    19:30    0    1174500    1725000    1175000    1725500    WW    0    40
11:30    11:30    0    1168000    1738500    1168000    1738000    HW    0    50

 

예를 들어 혁신도시라는 구역은 좌표가 아래와 같이 표기 되어 있는 것이죠, 출발 시간대에 여기의 좌표 내에 있는 것은 모두 혁신도시에서 출발을 했다는 것이고요

 gubun        | x_5179            | y_5179            |
+--------------+-------------------+-------------------+
| 혁신도시     | 1162436.625000000 | 1730989.250000000 |
| 혁신도시     | 1162436.125000000 | 1731089.250000000 |
| 혁신도시     | 1162435.875000000 | 1731139.250000000 |
| 혁신도시     | 1162488.125000000 | 1730689.625000000 |
| 혁신도시     | 1162487.875000000 | 1730739.625000000 |
| 혁신도시     | 1162487.625000000 | 1730789.625000000 |
| 혁신도시     | 1162487.125000000 | 1730889.625000000 |
| 혁신도시     | 1162486.125000000 | 1731089.500000000 |
| 혁신도시     | 1162485.875000000 | 1731139.500000000 |
| 혁신도시     | 1162485.625000000 | 1731189.500000000 |
+--------------+-------------------+-------------------+

 

현재, 지역에 대한 데이터는 아래와 같이 정리가 되어 있습니다.

MariaDB [jwkim]> select * from hangjung_tb limit 5;
+-----------+-----------+------------+------------+-------------------+-------------------+
| adm_dr_cd | adm_dr_nm | sigungu_cd | sigungu_nm | x_5179            | y_5179            |
+-----------+-----------+------------+------------+-------------------+-------------------+
|   2601051 | 학성동    |      26010 | 중구       | 1165688.875000000 | 1730455.875000000 |
|   2601051 | 학성동    |      26010 | 중구       | 1165687.375000000 | 1730755.750000000 |
|   2601051 | 학성동    |      26010 | 중구       | 1165738.875000000 | 1730456.125000000 |
|   2601051 | 학성동    |      26010 | 중구       | 1165738.625000000 | 1730506.125000000 |
|   2601051 | 학성동    |      26010 | 중구       | 1165738.375000000 | 1730556.125000000 |
+-----------+-----------+------------+------------+-------------------+-------------------+

 

 

제가 원하는 것은, 아래와 같이 유입인구를 찾을 때 시간대 00:00 , 00:30 (30분 단위 ulsan_od_2021) 은 1시 이내로 보고 

아래의 결과물을 one query로 만드는 것인데 쉽지가 않네요... 여러분들의 도움을 구합니다..!

 

예시) 결과물의 최종 형태

혁신도시 유입 인구                          
시간대  합계 중구
학성동 반구1동 반구2동 복산1동 복산2동 성안동 중앙동 우정동 태화동 다운동 병영1동 병영2동 약사동
계 　 　 　 　 　 　 　 　 　 　 　 　 　 　
1 　 　 　 　 　 　 　 　 　 　 　 　 　 　
2 　 　 　 　 　 　 　 　 　 　 　 　 　 　
3 　 　 　 　 　 　 　 　 　 　 　 　 　 　
by 마농 [2022.11.29 08:36:55]

샘플 자료를 인과관계에 맞게 정확하게 보여주시면 좋을 듯 합니다.
원본 대비 결과표
DB 종류 및 버전도 알려주세요.

by 김정완 [2022.11.29 10:00:47]

https://1drv.ms/u/s!AsL2wfnC-6dLppw8h2d3UGjUabfSZA?e=E4ZgdR

 

이 공유 폴더에 접속하시면 

완료해야 하는 표는 (양식)으로 끝나는 두개의 엑셀 파일 입니다.

 

ulsan_od_2021 이 테이블이 메인 테이블 입니다. 

oritime 출발시간
destime 도착시간
triptime 이동시간
orix 출발지 x좌표
oriy 출발지 y좌표
desx 도착지 x좌표
dexy 도착지 y좌표
odtype 무시해도 됩니다
age 무시해도 됩니다
popu 총 이동인구수

이고, 이것과 관련하여 

new_move.xlsx에 있는 좌표가 혁신도시의 좌표입니다.

그래서 여기가 출발지가 되어 도착지가 잡히는 부분과

여기가 도착지가 되어 출발지가 잡히는 부분을 sql 하는 것이 최종 목표 입니다.

by 마농 [2022.11.29 10:29:54]

그런 설명 말고 실제 샘플 데이터요.
위에 각 테이블별 예시가 몇건씩 있긴 한데, 데이터가 서로 연결되는 데이터가 아니네요.
지금 자료는 서료 연결도 안되고 결과집합은 비어 있는 상태인데.
이 상태로는 테이블간 어떤 조건으로 연결이 되는지 설명이 부족합니다.
서료 연결이 되는 샘플 데이터와 그에 따른 채워진 결과집합을 보여주세요.

by 김정완 [2022.11.29 11:16:56]

select a.oritime , b.adm_dr_nm , sum(a.popu)
from ulsan_od_2021 a , hangjung_tb b
where a.orix = b.x_5179
and a.oriy = b.y_5179
group by a.oritime, b.adm_dr_nm
;

일단, 이렇게 짜서 pivot이 되면 되는데 pivot은 방법을 잘 모르는 상황입니다.

결론적으로 해당 where 절에 좌표계를 join해야 하는데 

좌표의 범위가 어느 동이다가 나온 것이 hangjung의 의미 입니다.

 

위의 쿼리로 돌리니 무한대의 시간이 걸려서,, 문의 드리게 되었습니다!

 

by 마농 [2022.11.29 14:48:18]

이런식의 추상적이고 모호한 질문으로는 해결할 수 없습니다.
질문이 더 구체적이어야 하고, 모호한 표현을 커버해 주는게 샘플 데이터 입니다.
단순히 피벗만을 원한다면 쿼리를 추상적으로 표현해 보겠습니다.
 

SELECT 시간대
     , COUNT(CASE WHEN 동네 = '학성동' THEN 1 END) 학성동
     , COUNT(CASE WHEN 동네 = '성안동' THEN 1 END) 성안동
     , ...
  FROM ...
 WHERE ...
 GROUP BY 시간대
;

 

by 마농 [2022.11.30 16:30:23]

행정동은 동적으로 변하는 값이라 이를 행열변환 처리하려면 동적쿼리를 사용해야 하고 복잡합니다.
결과표의 형열을 바꾸는게 편할 듯 한데요.
시간대가 옆으로 나열되고
동네가 아래로 나열되는 형태
이러면 시간대는 24개 고정값이라 구현하기 쉽습니다.
고객을 한번 설득해 보세요.

SELECT 행정동
     , COUNT(CASE WHEN 시간대 =  0 THEN 1 END) "00"
     , COUNT(CASE WHEN 시간대 =  1 THEN 1 END) "01"
     , ...
     , COUNT(CASE WHEN 시간대 = 23 THEN 1 END) "23"
  FROM ...
 WHERE ...
 GROUP BY 행정동
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입