좋은 개발자가 되자

1. Excel (엑셀)에서 유용하게 사용하는 sumproduct 함수는 Power BI (파워비아이)에서 SUMX라는 함수로 제공한다

대체 왜... 그 훌륭하고 사람들이 많이 사용해 익숙한 엑셀의 sumproduct를 이름을 바꿔놔서 헷갈리게 하는지... 마소의 네이밍은 정말... 아무튼 그러합니다.

2. RELATED 로 테이블 2개 연결하기

엑셀은 파일 하나에 여러 테이블(탭)을 만들고 손쉽게 가져다 사용할 수 있다.

하지만 Power BI는 그게 안 된다. 원본을 엑셀을 사용하든, 데이터베이스를 사용하든 SUMX 계산식만으로는 서로 다른 테이블을 연결할 수가 없다. RELATED를 이용해 두 테이블을 연결해 계산하는 방법을 소개한다.

1 ) 테이블 생성하기

홈에서 'Enter data'로 들어가거나 모델링에서 '새 테이블'로 들어가 TotalCost라는 테이블을 생성한다.

2-1 ) 테이블에 '새 측정값'으로 SUMX 함수를 이용해 값 계산하기

# SUMX Syntax
SUMX('테이블', 수식)

2-2 ) 서로 다른 테이블을 연결하기 위해 RELATED 이용하기

# SUMX with RELATED
TotalCost = SUMX('Sales', [Qty] * RELATED(Product[Cost]))

설명하자면, TotalCost라는 테이블을 만들고, 그 테이블에 SUMX 함수를 사용해 'Sales'테이블의 [Qty]열과 RELATED 함수를 사용해 'Product'테이블의 [Cost] 컬럼을 * 곱연산 한다.

Tag. powerbi sumproduct, powerbi sum product, power bi sumproduct, power bi sum product, 파워비아이 sumproduct, 파워비아이 sum product

맥도 iOS만큼은 아니어도 샌드박스 구조에 의해 앱 감시도 되고, 앱의 권한이나 보안에 굉장히 뛰어난 편이다.

그 중 알 수 없는 앱의 실행을 막아 보호하는 기능이 'Gatekeeper(게이트키퍼)'다.

이 게이트키퍼는 기본적으로 알 수 없는 앱이 실행되면 'ㅇㅇㅇ은(는) 맥 확인되지 않은 개발자가 배포했기 때문에 열 수 없습니다.'라는 문구를 띄우고, 시스템 환경설정 > 보안 및 개인 정보 보호 > 일반에서 권한을 부여해줄 수 있다.

하지만 GUI 상에서 게이트키퍼의 기능은 일반 사용자용으로 굉장히 제한적으로만 이용이 가능하다.

몇 가지 기능을 소개한다.

1. 게이트키퍼 활성화/비활성화

# Gatekeepar 상태 확인
spctl --status

# Gatekeeper 활성화
sudo spctl --master-enable

# Gatekeeper 비활성화
sudo spctl --master-disable

2. 규칙 등록 및 삭제하기

# 규칙 등록하기
sudo spctl --add --label "LabelName" /Path/apps

# 규칙 삭제하기
sudo spctl --remove --label "LabelName"

3. 규칙 확인하기

# 규칙 확인하기(너무 많이 나와서 확인하기도 어렵고, more를 사용 안 하면... 반드시 more를 추가로 명령하자)
spctl --list | more

# 규칙 확인하기(라벨 이용)
spctl --list --label "LabelName"

4. 규칙 관리하기

# 규칙 활성화
sudo spctl --enable --label "LabelName"

# 규칙 비활성화
sudo spctl --disable --label "LabelName"

그냥 시스템 환경설정에서 마우스 클릭이 더 쉬운데 이게 왜 필요한가??

GUI상에서 활성화 하기 위해서는 일단 앱을 실행하고, 게이트키퍼에 의해 실행이 중단된 후 시스템 환경설정에 들어가 규칙을 등록해줘야 한다. 마우스로 하니까 쉽다! 문제는... 최근 빅서에서 구글드라이브 파일 스트림 실행만 하면 프리징이 너무 심해 도저히 사용할 수가 없다;;; 심지어 로그인 항목에 등록되어 부팅 되며 자동 실행되어 부팅 하자마자 프리징으로 인해 맥 사용 자체가 안 된다;; (구글이 직접 만든 앱인데 이따구...) 이 경우 'Shift'키를 누르고 안전 모드로 시동한 후 터미널을 통해 규칙 등록을 해주는데 사용할 수 있다.

Power Query M function reference - PowerQuery M | Microsoft Docs

DAX는... 너무 기능이 제한적이다...

코딩이나 엑셀에서는 내 맘대로 변수도 만들고 해서 사용할 수 있었는데 DAX만으로 처리하기에는 제약이 너무 심해서 테이블이 정말 완벽하지 않다면 사용하기 힘들었고... 게다가 데이터를 'DirectQuery' 방식으로(쿼리로) 조회에오는 경우는 엑셀을 넣어 돌리거나 데이터 가져오기 모드보다 제약이 더욱 심했다.

그 갈증을 해소해줄 만한게 M formula(M 수식)!!! 드디어 찾았다 ㅠㅠ

문제는 쿼리 편집기에서 자동완성도, 도움말도, 교정도 전혀 안 해주지만;;; 있는게 어딘지...

1. 엑셀처럼 =를 넣어 시작한다. (=를 안 넣으면 그냥 문자열로 인식한다. 엑셀처럼...)

M Language Let - PowerQuery M | Microsoft Docs

=
let  
    Source = Text.Proper("hello world")  
in  
    Source

 let으로 문장을 선언하고 in으로 결과를 출력한다.

즉, let에서 코딩하고 in으로 print를 한다???

2. 변수 사용 및 계산하기

=
let     x = 1 + 1,
        y = 2 + 2,     
        z = y + 1 
in
        x + y + z

위 함수는 let, in 대신 [ ] [ ] 를 사용할 수도 있다.

=
[     x = 1 + 1,
      y = 2 + 2,
      z = y + 1,
      result = x + y + z 
][result]

3. 리스트 사용하기

목록 함수 - PowerQuery M | Microsoft Docs

리스트를 사용할 수 있다!!

그 중 리스트에서 Distinct 함수를 사용하는 예제다.

= List.Distinct({1, 1, 2, 3, 3, 3})

애로우 함수, 애로우 펑션, 화살표 함수, stringbuilder, strinbuffer, scanner, 스트링빌더, 스트링버퍼, 스캐너

4. Arrow function & Scanner (함수선언 및 사용자 값 입력 받기)

=
let  
    MyFunction = (parameter1, parameter2) => (parameter1 + parameter2) / 2  
in  
    MyFunction

이런 것도 된다!!!

21, 53을 넣고 '호출'을 누르면

참고로 '쿼리1'은 이름을 변경하지 않아서 저렇게 나오는거고, 이름을 바꿀 수도 있다.

이외에도 다양한 M formula가 있다. 개인적으로 DAX보다 유용한 것 같다.

참고 : '측정값'에서 테이블과 컬럼을 참조할 때는(Power BI에서 테이블은 쿼리, 컬럼은 필드로 부른다.) '테이블'[컬럼]의 형태를 사용했으나 M formula에서는 테이블에 ' '를 사용하지 않는다.

Tag. power bi m function, power bi m 공식, power bi m공식, power bi m formula, power bi 포뮬라, powerbi 포뮬라, 파워비아이 포뮬라, 파워비아이 m 포뮬라, power bi m 포뮬라, power bi m 포뮬라, 포뮬러

올 해 이전에 가입한 회원(작년까지 가입한 회원)을 조회하고 싶어서 조건을 걸려고 한다.

이 때 여러가지 방법을 시도해봤는데 SQL 의 속도는 다음과 같았다. (DB 성능과 데이터 양에 따라 절대 시간은 차이가 나고 속도 비율만 보면 됩니다.)

하드코딩

AND dm.MemberRegisterDate < '2020-01-01'

>> 4.2s

수식을 비교 연산자에 직접 대입

AND dm.MemberRegisterDate < (CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 23))

>> 4.2s

수식을 통해 변수에 값을 저장하고 사용

DECLARE @ThisYearFirstDay VARCHAR(10)
SET @ThisYearFirstDay = (CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 23))

...

AND dm.MemberRegisterDate < @ThisYearFirstDay

>> 2분 넘어가서 그냥 취소함...

변수에 담아두면 재사용성도 좋고 성능은 수식에 직접 대입한 것과 같을줄 알았는데 아니었다...

1. 올 해 시작일

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS 'FirstDay'                                               -- 'yyyy-dd-mm hh:mm:ss' 포맷

-- VARCHAR로 변경하기
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 23) AS 'FirstDay'                     -- 'yyyy-mm-dd' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 111) AS 'FirstDay'                    -- 'yyyy/mm/dd' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 102) AS 'FirstDay'                    -- 'yyyy.mm.dd' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 112) AS 'FirstDay'                    -- 'yyyymmdd' 포맷

SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 105) AS 'FirstDay'                    -- 'dd-mm-yyyy' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 103) AS 'FirstDay'                    -- 'dd/mm/yyyy' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 104) AS 'FirstDay'                    -- 'dd.mm.yyyy' 포맷
SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 105), '-', '') AS 'FirstDay'  -- 'ddmmyyyy' 포맷

SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 110) AS 'FirstDay'                    -- 'mm-dd-yyyy' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 101) AS 'FirstDay'                    -- 'mm/dd/yyyy' 포맷
SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 110), '-', '.') AS 'FirstDay' -- 'mm.dd.yyyy' 포맷
SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 110), '-', '') AS 'FirstDay'  -- 'mmddyyyy' 포맷

2. 올 해 마지막일

SELECT DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1) AS 'LastDay'                                               -- 'yyyy-dd-mm hh:mm:ss' 포맷

-- VARCHAR로 변경하기
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 23) AS 'LastDay'                     -- 'yyyy-mm-dd' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 111) AS 'LastDay'                    -- 'yyyy/mm/dd' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 102) AS 'LastDay'                    -- 'yyyy.mm.dd' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 112) AS 'LastDay'                    -- 'yyyymmdd' 포맷

SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 105) AS 'LastDay'                    -- 'dd-mm-yyyy' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 103) AS 'LastDay'                    -- 'dd/mm/yyyy' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 104) AS 'LastDay'                    -- 'dd.mm.yyyy' 포맷
SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 105), '-', '') AS 'LastDay'  -- 'ddmmyyyy' 포맷

SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 110) AS 'LastDay'                    -- 'mm-dd-yyyy' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 101) AS 'LastDay'                    -- 'mm/dd/yyyy' 포맷
SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 110), '-', '.') AS 'LastDay' -- 'mm.dd.yyyy' 포맷
SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 110), '-', '') AS 'LastDay'  -- 'mmddyyyy' 포맷

참고

작년, 올해, 내년의 시작일과 마지막일

SELECT DATEADD(YEAR, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AS 'LastYearFirstDay';
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1) AS 'LastYearLastDay';

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS 'FirstDay';
SELECT DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1) AS 'LastDay';

SELECT DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), 0) AS 'NextYearFirstDay';
SELECT DATEADD(YEAR, 1, DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1)) AS 'to do NextYearFirstDay';

이번달 시작일과 마지막일

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1);

SELECT CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));
SELECT CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1));

DECLARE @FirstDOM DATETIME, @LastDOM DATETIME
SET @FirstDOM = (SELECT CONVERT(DATE, DATEADD(dd, -(DAY(GETDATE())-1), GETDATE())))
SET @LastDOM = (SELECT DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE())+1,0))) 
SELECT @FirstDOM,@LastDOM;

1년 전 시작일, 2년 전 시작일

SELECT DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));
SELECT DATEADD(YEAR, -2, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));

오늘 00:00:00, 23:59:59

SELECT DATEADD(d,0,DATEDIFF(d,0,GETDATE()));
SELECT DATEADD(s,-1,DATEDIFF(d,-1,GETDATE()));

Tag. sql this month first day, sql this month last day. sql this year first day, sql this year last day, sql next year first day, sql next year last day, sql last year first day, sql last year last day, sql last year, sql recent year, sql last 1 year. sql recent 1 year, sql last 2 years, sql recent 2 years

Mac에서 NVRAM 또는 PRAM 재설정하기 - Apple 지원

Mac의 SMC를 재설정하는 방법 - Apple 지원

기종별로 방법이 다르니 홈페이지에서 확인하세요.

The Official Cyber Monday Mac Bundle Ft. Parallels Pro & Luminar 4 | StackSocial

데이터프레임 생성까지의 예제입니다.

기본적으로 csv로 출력하도록 하였으나 바로 DB로 밀어 넣고 싶다면 sqlalchemy를 사용하면 됩니다.

1. 샘플 - 작은 DimDate

import pandas as pd
pd.set_option('display.max_row', 100)

def create_date_table(start, end):
    start_ts = pd.to_datetime(start).date()

    end_ts = pd.to_datetime(end).date()

    # record timetsamp is empty for now
    dates =  pd.DataFrame(columns=['Record_timestamp'],
        index=pd.date_range(start_ts, end_ts))
    dates.index.name = 'Date'

    days_names = {
        i: name
        for i, name
        in enumerate(['월', '화', '수', '목', '금', '토', '일'])
    }

    dates['DayName'] = dates.index.dayofweek.map(days_names.get)
    dates['Week'] = dates.index.week
    dates['Month'] = dates.index.month
    dates['Quarter'] = dates.index.quarter
    dates['YearHalf'] = dates.index.month.map(lambda mth: 1 if mth <7 else 2)
    dates['Year'] = dates.index.year
    dates.reset_index(inplace=True)
#     dates.index.name = 'DateKey'
    dates.drop(['Record_timestamp'], axis=1, inplace=True)
    return dates

def fullDateKor(inColumn):
    tmpDate = str(inColumn)[:10].split('-')
    outValue = tmpDate[0] + "년 " + tmpDate[1] + "월 " + tmpDate[2] + "일"
    
    return outValue

def dateKey(inColumn):
    return str(inColumn)[:10].replace('-', '')

df = create_date_table(start='1900-01-01', end='2100-12-31')

df['FullDateKor'] = df.Date.apply(fullDateKor)
df['DateKey'] = df.Date.apply(dateKey)

df

# df.to_csv("./DimDate.csv", index=False)

2. 샘플 - 큰 DimDate

import pandas as pd
pd.set_option('display.max_row', 500)
pd.set_option('display.max_columns', 500)

def create_date_table(start, end):
    start_ts = pd.to_datetime(start).date()

    end_ts = pd.to_datetime(end).date()

    # record timetsamp is empty for now
    dates =  pd.DataFrame(columns=['Record_timestamp'],
        index=pd.date_range(start_ts, end_ts))
    dates.index.name = 'FullDateAlternateKey'

    months_names_kor = {
        i+1: name
        for i, name
        in enumerate(['1월','2월','3월','4월','5월','6월','7월','8월','9월','10월','11월','12월'])
    }
    
    months_names_eng = {
        i+1: name
        for i, name
        in enumerate(['January','February','March','April','May','June','July','August','September','October','November','December'])
    }
    
    days_names_kor = {
        i: name
        for i, name
        in enumerate(['월요일', '화요일', '수요일', '목요일', '금요일', '토요일', '일요일'])
    }
    
    days_names_eng = {
        i: name
        for i, name
        in enumerate(['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'])
    }
    
    # 월
    dates['MonthNumberOfYear'] = dates.index.month
    dates['KoreanMonthName'] = dates.index.month.map(months_names_kor.get)
    dates['EnglishMonthName'] = dates.index.month.map(months_names_eng.get)

    # 일
    dates['DayNumberOfWeek'] = dates.index.dayofweek.map(lambda x: x + 2 if x < 6 else 1) # {월 : 0 ~ 일 : 6}을 {일 : 1 ~ 토 : 7}로 바꾼다.
    dates['KoreanDayNameOfWeek'] = dates.index.dayofweek.map(days_names_kor.get)
    dates['EnglishDayNameOfWeek'] = dates.index.dayofweek.map(days_names_eng.get)
    
    dates['CalendarYear'] = dates.index.year # 연도
    dates['CalendarYearHalf'] = dates.index.month.map(lambda mth: 1 if mth <7 else 2) # 반기
    dates['CalendarQuarter'] = dates.index.quarter # 분기

    dates['WeekNumberOfYear'] = dates.index.week # 주차 : 1 ~ 52
    dates['DayNumberOfMonth'] = dates.index.day # 월별 일 : 1 ~ 31
    dates['DayNumberOfYear'] = dates.index.dayofyear # 연간 일수 : 1 ~ 365
    
    dates.reset_index(inplace=True)
    dates.index.name = 'DateKey'
    dates.drop(['Record_timestamp'], axis=1, inplace=True)
    return dates


def dateKey(inColumn):
    return str(inColumn)[:10].replace('-', '')

def fullDateKor(inColumn):
    tmpDate = str(inColumn)[:10].split('-')
    outValue = tmpDate[0] + "년 " + tmpDate[1] + "월 " + tmpDate[2] + "일"
    
    return outValue

df = create_date_table(start='1900-01-01', end='2100-12-31')

df['DateKey'] = df.FullDateAlternateKey.apply(dateKey)
df['FullDateKor'] = df.FullDateAlternateKey.apply(fullDateKor)

# 컬럼 순서 재정렬
df = df.reindex(columns=['DateKey'
                         ,'FullDateAlternateKey'
                         ,'FullDateKor'
                         
                         ,'MonthNumberOfYear'
                         ,'KoreanMonthName'
                         ,'EnglishMonthName'
                         
                         ,'DayNumberOfWeek'
                         ,'KoreanDayNameOfWeek'
                         ,'EnglishDayNameOfWeek'
                         
                         ,'CalendarYear'
                         ,'CalendarYearHalf'
                         ,'CalendarQuarter'
                         
                         ,'WeekNumberOfYear'
                         ,'DayNumberOfMonth'
                         ,'DayNumberOfYear'
                        ])

df

# df.to_csv("./DimDate.csv", index=False, encoding='UTF-8')
# pd.read_csv('./DimDate.csv')

(클릭 시 원본 확인 가능)

SqlAlchemy 사용법 확인하기

Tag. date dimension, date 차원, date 테이블, dim date