CodeKata

SQL

176. SQL Project Planning

• 작성한 쿼리

SELECT
  start_date
  , MIN(end_date)
FROM
  (
  SELECT
    start_date
  FROM
    projects
  WHERE
    start_date NOT IN (SELECT end_date FROM projects)
  ) AS a 
  JOIN
  (
  SELECT
    end_date
  FROM
    projects
  WHERE
    end_date NOT IN (SELECT start_date FROM projects)
  ) AS b
WHERE
  start_date < end_date
GROUP BY
  start_date
ORDER BY
  MIN(end_date)-start_date
  , start_date
;

참고할 만한 다른 쿼리

1. window function

SELECT
  MIN(Start_Date)
  , MAX(End_Date)
FROM
  (
  SELECT
    Start_Date
    , End_Date
    , Start_Date - ROW_NUMBER() OVER(ORDER BY Start_Date) as rnk
  FROM
    Projects
  )l
GROUP BY
  rnk
ORDER BY
  DATEDIFF(MAX(End_Date),MIN(Start_Date))
  , MIN(Start_Date)
;

• 10월 1일 부터 시작하기에 연속적으로 시작과 끝이 이어진다면 5일==5등이 와야하겠지만 그렇지 않다. (11일이 5등임)
  • 그 뜻은 11-5=6, 그 동안의 텀이 있었다는 것!
  • 4일로 부터 6일이 흐른뒤에 11일에 다시 프로젝트를 시작했다.
• 연속적으로 이어지는 12일도 순위 6을 빼면 5일로 같은 프로젝트임을 알 수 있다.
  • 이 논리를 이용해 rnk를 구하고 같은 프로젝트인 것끼리 그룹화(rnk)
  • 그 그룹에서 최소의 start_date와 end_date를 입력해주면 프로젝트의 시작 날짜와 끝나는 날짜를 알 수 있음
• 정렬할 때 DATEDIFF(MAX(End_Date),MIN(Start_Date)) 이 자리에 COUNT(*)를 써도 된다고 함

with cte as (
  select 
    groupdates
    , start_date
    , end_date
    , count(groupdates) over(partition by groupdates) as cnt 
  from (
    select
      *
      , row_number() over(order by start_date) as row_no
      , start_date - interval row_number() over(order by start_date) day as groupdates 
    from Projects) temp 
  order by 
    cnt
    , start_date
)
select 
  min(start_date)
  , max(end_date)
from 
  cte
group by
  groupdates
;

팀 프로젝트

하남 신도시 공유차량 적정 수요 예측

위치 데이터 시각화

참고

1. lat-lon 데이터

• 위도(latitude)-경도(longitude)
• 지도 상에 위치를 찍는 가장 기본적이고 간단한 방법

• Map 시각화 기능을 갖고 있는 대부분의 Tool들은 Columns에 Longitude, Rows에 Latitude 세팅하면 알아서 지도에 표출해 줌

2. Polygon Map

• Tableau 자체 지도에서 기본적인 Coutry나, State 단위의 맵핑은 Country명, State명 만으로도 가능
  • 해당 기능을 이용하면 지도상에 지역별로 하이라이트하는 것까지 가능함
    

3. Spatial file

• Polygon 등의 map 정보를 담고 있는 파일을 말함
  • 대표적인 file type
    • GeoJSON
    • Shape file
  • map 정보를 담고 있는 파일을 GIS(Geographic Information System)에 올리면 바로 시각화된 지도로 표출됨
• 구, 동 같은 작은 지역별 구분은 lat-lon이나 Tableau 지원 지도만으로는 힘들기 때문에 사용
  

Layered(Dual Axis) Map on Tableau

1. shape file 올리기

   • 원하는 구획으로 그려진 Shape file을 올려보면 아래와 같이 구역이 나뉘어진 지도를 바로 볼 수 있음
     
     • 시-도별 구분이 필요할 수도 있고, 구별, 동별 구분이 필요할 수도 있으니 필요한 shape를 잘 선택할 것

2. Shape file의 geography 데이터와 분석 데이터의 lat-lon 연결

   • makepoint 함수
     - Shape file의 polygon 영역 안에 lat-lon 데이터 연결
     
   • 두 데이터간의 관계는 Intersects로
   • 양측 데이터 간에 교차하지 않는 지점도 모두 표출하고자 하는 경우 Full Outer Join 선택
     

Advanced : Spartial Index

• 기존 행정구역은 크기와 위치가 들쭉날쭉하며, 대부분의 그 단위가 세분화하기에는 너무 크기 때문에 위치 기반 서비스의 경우 기존 행정구역을 사용해 서비스 위치를 세분화하기 어려움
  • 고객의 위치를 정확하게 파악하고 구분하기 위해서 Grid System 사용
    • Geohash
      
    • H3
      
  • 지구상의 모든 지점을 identifiable한 가상의 grid cell로 파티셔닝

회고

• 팀 프로젝트 첫 날!
  • 생각했던 것보다 데이터를 더 살펴볼 수 있어서 좋았음
    • 원래 계획은 기획서 작성까지였는데 조원 모두 적극적으로 참여한 덕분에 기초 분석까지 했다!
  • 데이터 가공 조금만 감을 잡으면 해석과 보고(ppt, 태블로 대시보드) 부분은 걱정했던 것보다 빨리 해결될 수 있을 듯하다.
• 팀 프로젝트 진행하면서 항상 여유를 가질 수 있도록 배려하고 미리 해 둘 수 있는 것들은 준비해 두는 게 좋겠다!
  • PPT 틀 잡기
  • 발표 내용 정리하기