포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ COUNT Oracle Function ]
COUNT함수는 쿼리에 의해 반환된 행의 수를 반환한다. 집계 함수 또는 분석함수로 이용할 수 있다.
- 함수설명 : COUNT 오라클 함수 링크
- 157번 참조 Check whether all employees number are indeed unique.
- 유사문제 : 97 / 103 / 104 / 132
- Cross Join 생성(dummy) : 97 / 140
- fuzzyjoin::fuzzy_left_join : 113 / 116
1. Oracle(오라클)
- 다음 예제는 COUNT 함수를 집계함수로 이용.
1) 집계함수 예제
다음 함수는 테이블의 건수를 집계하는 집계 함수 예제이다.
| Oracle Programming |
SELECT COUNT(*) "Total" FROM employees;
SELECT COUNT(commission_pct) "Count" FROM employees;
SELECT COUNT(DISTINCT manager_id) "Managers" FROM employees;
SELECT COUNT(*) "Allstars" FROM employees WHERE commission_pct > 0;
| Results |
| Total ------------ 14 |
Count ------------ 4 |
Managers ------------ 6 |
Allstars ------------ 3 |
2) 분석함수 예제
개별 직원의 급여를 기준으로 일정 범위 내에 ( 직원 급여 - 50 ~ 직원 급여 + 150 ) 급여를 수령하는 직원들의 수를 집계한다.
| Oracle Programming |
SELECT ENAME, SAL,
COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND
150 FOLLOWING) AS mov_count
FROM EMP
ORDER BY SAL, ENAME;
| Results |
ENAME SAL MOV_COUNT
-------------------------
SMITH 800 2
JAMES 950 2
ADAMS 1100 3
MARTIN 1250 3
WARD 1250 3
MILLER 1300 3
TURNER 1500 2
ALLEN 1600 1
CLARK 2450 1
BLAKE 2850 4
JONES 2975 3
FORD 3000 3
SCOTT 3000 3
KING 5000 1
2. Python Pandas(파이썬)
1) 집계함수
| Python Programming |
#Total
display( emp.agg({'empno':['count']}) )
display( np.sum(emp['empno'].count()) )
| Results |
empno
count 14
| Results |
14
| Python Programming |
# count
display( emp['comm'].notnull().sum() ) # null을 제외한 값을 카운트
display( (~emp['comm'].isna()).sum() ) # na값을 제외한 값을 카운트
display( emp['comm'].notna().sum() ) # na값을 제외한 값을 카운트
| Results |
4
4
4
| Python Programming |
# Managers
display( emp.agg({'mgr':['nunique']}) ) # 중복을 제외한 값을 카운트
display( emp['mgr'].nunique() ) # 중복을 제외한 값을 카운트
| Results |
mgr
nunique 6
| Results |
6
| Python Programming |
# Allstars
emp[emp['comm']>0][['comm']].count() # 커미션(comm) 값이 양수인 값을 카운트
| Results |
comm 3
dtype: int64
2) 분석함수
| Python Programming |
withmooc = emp.copy()
# 현재 sal의 값을 기준으로 x-50 ~ x+150 사이의 값을 카운트
withmooc['range_cnt'] = withmooc['sal'].apply(lambda x: withmooc['sal'].between(x-50, x + 150, inclusive=True).sum())
withmooc.sort_values(by = 'sal').head(10)
| Results |
empno ename job mgr hiredate sal comm deptno range_cnt
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 2
11 7900 JAMES CLERK 7698.0 1981/12/03 950 NaN 30 2
10 7876 ADAMS CLERK 7788.0 1983/01/12 1100 NaN 20 3
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 3
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 3
13 7934 MILLER CLERK 7782.0 1982/01/23 1300 NaN 10 3
9 7844 TURNER SALESMAN 7698.0 1981/09/08 1500 0.0 30 2
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 1
6 7782 CLARK MANAGER 7839.0 1981/01/09 2450 NaN 10 1
5 7698 BLAKE MANAGER 7839.0 1981/03/01 2850 NaN 30 4
[참고]
| Python Programming |
withmooc = emp.copy()
# 현재 sal의 값을 기준으로 x-50 ~ x+150 사이의 포함된 값에 대한 합계
withmooc['range_sum'] = withmooc['sal'].apply(lambda x: ( withmooc['sal'][ withmooc['sal'].between(x-50, x + 150, inclusive=True) ] ).sum() )
withmooc.sort_values(by=['sal']).head()| Results |
empno ename job mgr hiredate sal comm deptno range_sum
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 1750
11 7900 JAMES CLERK 7698.0 1981/12/03 950 NaN 30 2050
10 7876 ADAMS CLERK 7788.0 1983/01/12 1100 NaN 20 3600
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 3800
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 3800
3) CROSS JOIN
- emp 테이블을 cross join 후 왼쪽 테이블의 기준 값을 기준으로 오른쪽 테이블에서 x-50 ~ x+150 사이에 포함된 값을 선택 후 카운트
| Python Programming |
pd.merge(emp.assign(foo=1), emp.assign(foo=1), on ='foo').query(' sal_y >= sal_x - 50 & sal_y <= sal_x + 150 ').groupby(['empno_x','sal_x'])['sal_y'].count().head()
| Results |
empno_x sal_x
7369 800 2
7499 1600 1
7521 1250 3
7566 2975 3
7654 1250 3
Name: sal_y, dtype: int64
3. R Programming (R Package)
| R Programming |
%%R
base::summary(emp$empno)
library(plyr)
plyr::summarise(emp, Total = length(empno), # empno 변수의 길이
count = sum(!is.na(comm)), # na을 제외한 값을 카운트
count_1 = length(which(!is.na(emp$comm))), # na을 제외한 값을 선택 후 해당 값의 길이
Managers = sum(!is.na(unique(mgr))) ) # 중복을 제외한 값을 카운트
| Results |
Total count count_1 Managers
1 14 4 4 6
| R Programming |
%%R
# Allstars
sum( !is.na(emp[emp$comm>0 , c("comm")]) ) # comm이 0보다 큰 값을 카운트
| Results |
[1] 3
2) 분석함수
| R Programming |
%%R
withmooc <- emp
# 현재 sal의 값을 기준으로 x-50 ~ x+150 사이의 값을 카운트
withmooc['range_cnt'] = sapply(emp$sal, function(x) length(emp$sal[between(emp$sal, x-50, x+150)]))
withmooc[order(withmooc$sal),][1:5,]
| Results |
# A tibble: 5 x 9
empno ename job mgr hiredate sal comm deptno range_cnt
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <int>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 2
2 7900 JAMES CLERK 7698 1981-12-03 950 NA 30 2
3 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20 3
4 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 3
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 3
3) CROSS JOIN
- merge 구문을 사용하여서 emp 테이블을 cross join(self join) 후 왼쪽 테이블의 기준 값을 기준으로 오른쪽 테이블에서 x-50 ~ x+150 사이에 포함된 값을 선택 후 카운트
| R Programming |
%%R
aggregate( sal.y ~ empno.x + sal.x, data = subset( merge(emp, emp,by= character(),all.x=TRUE, all.y=TRUE), between(sal.y, sal.x -50 , sal.x + 150) ),
FUN = function(x) c(count_sal = length(x) )
)[1:5, ]
| Results |
empno.x sal.x sal.y
1 7369 800 2
2 7900 950 2
3 7876 1100 3
4 7521 1250 3
5 7654 1250 3
4. R Dplyr Package
1) 집계함수
| R Programming |
%%R
emp %>%
tally() # emp 테이블의 전체 관측치 개수
| Results |
# A tibble: 1 x 1
n
<int>
1 14
| R Programming |
%%R
emp %>%
dplyr::summarize( Total = n() , # emp 테이블의 전체 관측치 개수
count = sum(!is.na(comm)), # na를 제외한 comm값을 카운트
Managers = n_distinct(mgr, na.rm = TRUE), # 중복을 제외한 값을 카운트
Managers_1 = sum(!is.na(unique(mgr))) ) # 중복을 제외한 값을 카운트
| Results |
# A tibble: 1 x 4
Total count Managers Managers_1
<int> <int> <int> <int>
1 14 4 6 6
| R Programming |
%%R
# Allstars
emp %>%
dplyr::filter( comm > 0 ) %>% # comm가 0보다 큰 데이터 선택
dplyr::tally() # filter로 선택 된 관측치 개수를 카운트
| Results |
# A tibble: 1 x 1
n
<int>
1 3
2) 분석함수
- https://stackoverflow.com/questions/47626378/referring-to-vector-vs-row-with-grouped-data-in-dplyr-r
| R Programming |
%%R
# 함수를 사용하여서 현재 sal의 값을 기준으로 x-50 ~ x+150 사이의 값을 선택 후 카운트
emp %>%
dplyr::arrange(sal) %>%
dplyr::mutate( year_guess = sapply(sal, function(x) length(sal[sal >= x - 50 & sal <= x + 150])) ) %>%
head()
| Results |
# A tibble: 6 x 9
empno ename job mgr hiredate sal comm deptno year_guess
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <int>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 2
2 7900 JAMES CLERK 7698 1981-12-03 950 NA 30 2
3 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20 3
4 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 3
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 3
6 7934 MILLER CLERK 7782 1982-01-23 1300 NA 10 3
- 함수를 사용하여서 현재 sal의 값을 기준으로 x-50 ~ x+150 사이의 값(between)을 선택 후 카운트
| R Programming |
%%R
emp %>%
dplyr::rowwise() %>%
dplyr::mutate(nhdd = sapply(sal, function(x) length(sal[between(sal, x -50, x + 150 )])) ) %>%
dplyr::arrange(sal) %>%
head()
| Results |
# A tibble: 6 x 9
# Rowwise:
empno ename job mgr hiredate sal comm deptno nhdd
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <int>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1
2 7900 JAMES CLERK 7698 1981-12-03 950 NA 30 1
3 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20 1
4 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1
6 7934 MILLER CLERK 7782 1982-01-23 1300 NA 10 1
- 함수를 사용하여서 현재 sal의 값을 기준으로 x-50 ~ x+150 사이에 속하는 급여(sal)의 합계를 계산
| R Programming |
%%R
emp %>%
dplyr::rowwise() %>%
dplyr::mutate(nhdd = sum(emp$sal[between(emp$sal, emp$sal-50, emp$sal + 50 )])) %>%
head()
| Results |
# A tibble: 6 x 9
# Rowwise:
empno ename job mgr hiredate sal comm deptno nhdd
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <dbl>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 29025
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 29025
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 29025
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 29025
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 29025
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 29025
[참고] dbplyr 패키지
: SQL 쿼리 자동 생성하여 동일한 결과를 산출하는 SQL 쿼리를 확인 할 수 있다. (현재 count(*) 와 RANGE BETWEEN 에 대하여 생성 안되는 듯)
| R Programming |
%%R
# install.packages("dbplyr")
library(dbplyr)
memdb_frame(emp) %>% # lazy_frame(emp)
dbplyr::window_frame(-50, 150) %>%
dbplyr::window_order(sal) %>%
dplyr::summarise( n_count= n() , sal_sum = sum(sal)) %>%
show_query() # sql_build()
| R Programming |
<SQL>
SELECT COUNT() AS `n_count`, SUM(`sal`) AS `sal_sum`
FROM `dbplyr_006`
| R Programming |
%%R
library(dbplyr)
lazy_frame(emp) %>% #memdb_frame(emp) %>%
dbplyr::window_frame(-1, 2) %>%
dbplyr::window_order(sal) %>%
dplyr::mutate(NEW_COUNT = sum(sal)) %>%
show_query()
| R Programming |
<SQL>
SELECT `empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`, SUM(`sal`) OVER (ORDER BY `sal` ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS `NEW_COUNT`
FROM `df`
| R Programming |
%%R
memdb_frame(emp) %>%
dplyr::arrange ( sal ) %>%
dbplyr::window_order ( sal ) %>%
dplyr::mutate ( cumsum = cumsum ( sal )) %>%
show_query ()
| R Programming |
<SQL>
SELECT `empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`, SUM(`sal`) OVER (ORDER BY `sal`, `sal` ROWS UNBOUNDED PRECEDING) AS `cumsum`
FROM (SELECT *
FROM `dbplyr_008`
ORDER BY `sal`)
| R Programming |
%%R
memdb_frame(emp) %>% # lazy_frame(emp)
dbplyr::window_frame(-50, 150) %>%
dbplyr::window_order(sal) %>%
dplyr::summarise( n_count= n() ) %>%
show_query() # sql_build()
| R Programming |
<SQL>
SELECT COUNT() AS `n_count`
FROM `dbplyr_010`
3) CROSS JOIN
- full_join 구문을 사용하여서 emp 테이블을 cross join 후 왼쪽 테이블의 기준 값을 기준으로 오른쪽 테이블에서 x-50 ~ x+150 사이에 포함된 값을 선택 후 카운트.
- full_join 구문으로 cross join을 수행하기 위하여 “by = character()” 를 지정
| R Programming |
%%R
emp %>%
dplyr::full_join(emp, by = character()) %>%
dplyr::filter( between(sal.y, sal.x - 50, sal.x + 150 ) ) %>%
dplyr::group_by(empno.x, sal.x) %>%
dplyr::summarize( Range_cnt = length(sal.y) ) %>%
dplyr::arrange(sal.x) %>%
head()
| Results |
`summarise()` regrouping output by 'empno.x' (override with `.groups` argument)
# A tibble: 6 x 3
# Groups: empno.x [6]
empno.x sal.x Range_cnt
<dbl> <dbl> <int>
1 7369 800 2
2 7900 950 2
3 7876 1100 3
4 7521 1250 3
5 7654 1250 3
6 7934 1300 3
5. R sqldf Package
1) 집계함수
| R Programming |
%%R
print( sqldf(" select count(*) as Total FROM emp ") ) # emp 테이블의 전체 관측치 개수
print( sqldf(" SELECT COUNT(comm) count FROM emp ") ) # NULL을 제외한 comm의 값을 카운트
print( sqldf(" SELECT COUNT(DISTINCT mgr) Managers FROM emp ") ) # 중복을 제외한 값을 카운트
print( sqldf(" SELECT COUNT(*) Allstars FROM emp WHERE comm > 0 ") ) # 0보다 큰 comm의 값을 카운트
| Results |
Total
1 14
count
1 4
Managers
1 6
Allstars
1 3
2) 분석함수
* RANGE BETWEEN
- RANGE BETWEEN를 사용하여서 현재 sal의 값을 기준으로 x-50 ~ x+150 사이에 속하는 급여(sal)의 합계를 계산
| R Programming |
%%R
sqldf(" SELECT ename, sal,
COUNT(*) OVER ( ORDER BY sal RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS mov_count
FROM emp
ORDER BY sal, ename; ")[1:5, ]
| Results |
ename sal mov_count
1 SMITH 800 2
2 JAMES 950 2
3 ADAMS 1100 3
4 MARTIN 1250 3
5 WARD 1250 3
[참고] ROWS BETWEEN
- ROWS BETWEEN를 사용하여서 현재 sal의 값을 기준으로 x 이전 관측치 ~ x 이후 2개의 관측치의 개수를 카운트
| R Programming |
%%R
sqldf(" SELECT `empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`,
COUNT(*) OVER (ORDER BY `SAL` ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS `xxx`
FROM emp
ORDER BY SAL; ")[1:5,]
| Results |
empno ename job mgr hiredate sal comm deptno xxx
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 3
2 7900 JAMES CLERK 7698 1981-12-03 950 NA 30 4
3 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20 4
4 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 4
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 4
3) 비등가 JOIN
- 비등가 Cross join을 수행하여서 현재 sal의 값을 기준으로 x-50 ~ x+150 사이의 값을 선택 후 카운트
| R Programming |
%%R
sqldf(" SELECT A.ENAME,A.SAL,B.mov_count
FROM EMP A
LEFT JOIN ( SELECT a.sal,count(distinct b.empno) mov_count
FROM emp a, emp b
where b.sal between a.sal - 50 and a.sal + 150
group by a.sal ) B
ON A.SAL = B.SAL
ORDER BY 2,1")[1:5, ]
| Results |
ename sal mov_count
1 SMITH 800 2
2 JAMES 950 2
3 ADAMS 1100 3
4 MARTIN 1250 3
5 WARD 1250 3
6. Python pandasql Package
1) 집계함수
| Python Programming |
print( ps.sqldf(" select count(*) as Total FROM emp ") )
print( ps.sqldf("SELECT COUNT(comm) count FROM emp") )
print( ps.sqldf(" SELECT COUNT(DISTINCT mgr) Managers FROM emp ") )
print( ps.sqldf(" SELECT COUNT(*) Allstars FROM emp WHERE comm > 0 ") )
| Results |
Total
0 14
count
0 4
Managers
0 6
Allstars
0 3
2) 분석함수
- RANGE BETWEEN를 사용하여서 현재 sal의 값을 기준으로 x-50 ~ x+150 사이에 속하는 급여(sal)의 합계를 계산
| Python Programming |
ps.sqldf(" SELECT ename, sal, \
COUNT(*) OVER (ORDER BY sal RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS mov_count \
FROM emp \
ORDER BY sal, ename; ").head()
| Results |
ename sal mov_count
0 SMITH 800 2
1 JAMES 950 2
2 ADAMS 1100 3
3 MARTIN 1250 3
4 WARD 1250 3
3) 비등가 JOIN
- 비등가 Cross join을 수행하여서 현재 sal의 값을 기준으로 x-50 ~ x+150 사이의 값을 선택 후 카운트
| Python Programming |
ps.sqldf(" SELECT A.ENAME,A.SAL,B.mov_count \
FROM EMP A \
LEFT JOIN ( SELECT a.sal,count(distinct b.empno) mov_count \
FROM emp a, emp b \
where b.sal between a.sal - 50 and a.sal + 150 \
group by a.sal ) B \
ON A.SAL = B.SAL \
ORDER BY 2,1 ").head()
| Results |
ename sal mov_count
0 SMITH 800 2
1 JAMES 950 2
2 ADAMS 1100 3
3 MARTIN 1250 3
4 WARD 1250 3
7. R data.table Package
1) 집계함수
| R Programming |
%%R
library(data.table)
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[, .(sal_tot_1 = length(empno) , # emp 테이블의 전체 관측치 개수
sal_tot_2 = sum(!is.na(comm)), # na을 제외한 comm의 값을 카운트
sal_tot_3 = length(which(!is.na(emp$comm))), # na을 제외한 comm의 값을 카운트
mgr_tot = sum(!is.na(unique(mgr))) ) ] # 중복을 제외한 값을 카운트
| Results |
sal_tot_1 sal_tot_2 sal_tot_3 mgr_tot
1: 14 4 4 6
2) 분석함수
- 함수를 사용하여서 현재 sal의 값을 기준으로 x-50 ~ x+150 사이의 값(between)을 선택 후 카운트
| R Programming |
%%R
library(data.table)
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT <- DT[order(sal),]
DT[, cumsum := sapply(sal, function(x) length(sal[between(sal, x-50, x+150)])), ][1:5, ]
| Results |
empno ename job mgr hiredate sal comm deptno cumsum
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 2
2: 7900 JAMES CLERK 7698 1981-12-03 950 NA 30 2
3: 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20 3
4: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 3
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 3
3) CROSS JOIN
- emp 테이블(DT)을 cross join( = Cartesian Join) 후 왼쪽 테이블의 기준 값을 기준으로 오른쪽 테이블에서 x-50 ~ x+150 사이에 포함된 값을 선택 후 카운트.
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
# DT syntax 방식 Join
DT[,k:=1][DT[,k:=1], allow.cartesian=TRUE,on = .(k),][ i.sal >= sal -50 & i.sal <= sal + 150 , .('Range_count' = .N) , keyby = .(empno, sal)][1:5, -c("hiredate") ]
| Results |
empno sal Range_count
1: 7369 800 2
2: 7499 1600 1
3: 7521 1250 3
4: 7566 2975 3
5: 7654 1250 3
| R Programming |
%%R
# data.table::merge() syntax 방식 Join
merge(DT[,k:=1],DT[,k:=1], by="k",allow.cartesian=TRUE)[ sal.y >= sal.x -50 & sal.y <= sal.x + 150 , .('Range_count' = .N) , keyby = .(empno.x, sal.x)][1:5, ]
| Results |
empno.x sal.x Range_count
1: 7369 800 2
2: 7499 1600 1
3: 7521 1250 3
4: 7566 2975 3
5: 7654 1250 3
4) 비등가 JOIN
- 기준(왼쪽) 테이블에 x-50 ~ x+150에 속하는 급여(sal)의 최소값과 최대값을 생성 후 비등가 Cross join을 수행하여서 기준 관측치에 생성된 최소값과 최대값 사이에 포함되는 값을 선택 후 카운트
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[, c("sal_min", "sal_max") := list( sal - 50, sal+150)][DT,on = .( sal_min <= sal , sal_max >= sal ), ][ , .('Range_count' = .N) , keyby = .(empno, sal)][1:5, ]
| Results |
empno sal Range_count
1: 7369 800 2
2: 7499 1600 1
3: 7521 1250 3
4: 7566 2975 3
5: 7654 1250 3
[참고] Windows 함수 관련 패키지
: RcppRoll / zoo / slider / zoo::rollapply() / tibbletime::rollify() / tsibble::slide()
- https://github.com/DavisVaughan/slider
- frollsum 함수 : data.talble 패키지에서 사용가능한 함수로 빠른 속도로 이동합계을 계산한다.
- 이동평균(frollmean) / 이동곱(frollprod) / 이동합계(frollsum)
| R Programming |
%%R
# rollmean of single vector and single window
DT[, sum1 := data.table::frollsum(DT[, sal], 3, fill = 0)][, -c("job","hiredate")][1:10, ]
| Results |
empno ename mgr sal comm deptno sal_min sal_max sum1
1: 7369 SMITH 7902 800 NA 20 750 950 0
2: 7499 ALLEN 7698 1600 300 30 1550 1750 0
3: 7521 WARD 7698 1250 500 30 1200 1400 3650
4: 7566 JONES 7839 2975 NA 20 2925 3125 5825
5: 7654 MARTIN 7698 1250 1400 30 1200 1400 5475
6: 7698 BLAKE 7839 2850 NA 30 2800 3000 7075
7: 7782 CLARK 7839 2450 NA 10 2400 2600 6550
8: 7788 SCOTT 7566 3000 NA 20 2950 3150 8300
9: 7839 KING NA 5000 NA 10 4950 5150 10450
10: 7844 TURNER 7698 1500 0 30 1450 1650 9500
- 현재 sal의 값을 기준으로 x-50 ~ x+150 사이에 속하는 급여(sal)의 합계를 계산
| R Programming |
%%R
sqldf(" SELECT ename, sal,
sum(sal) OVER ( ORDER BY sal RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS mov_count
FROM emp
ORDER BY sal, ename; ")[1:5, ]
| Results |
ename sal mov_count
1 SMITH 800 1750
2 JAMES 950 2050
3 ADAMS 1100 3600
4 MARTIN 1250 3800
5 WARD 1250 3800
SQL 자동 생성 기능
: 아직 “RANGE BETWEEN” 는 지원안하는 듯 함(ROWS BETWEEN는 지원)
| R Programming |
%%R
memdb_frame(emp) %>% # lazy_frame(emp) %>%
dbplyr::window_frame(-50, 150) %>%
dbplyr::window_order(sal) %>%
dplyr::mutate(mov_sum = sum(sal)) %>%
show_query()
| Results |
<SQL>
SELECT `empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`, SUM(`sal`) OVER (ORDER BY `sal` ROWS BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS `mov_sum`
FROM `dbplyr_011`
8. Python DuckDB의 SQL
1) 집계 함수
| Python Programming |
%%sql
SELECT COUNT(*) "Total" FROM emp;
| Python Programming |
duckdb.sql(" SELECT COUNT(*) as Total FROM emp ").df()
| Results |
Total
0 14
| Python Programming |
%%sql
SELECT COUNT(comm) as count_1,
COUNT(DISTINCT mgr) as Managers_cnt
FROM emp;
| Python Programming |
duckdb.sql(" SELECT COUNT(comm) as count_1, \
COUNT(DISTINCT mgr) as Managers_cnt \
FROM emp ").df()
| Results |
count_1 Managers_cnt
0 4 6
| Python Programming |
%%sql
SELECT COUNT(*) "Allstars" FROM emp WHERE comm > 0;
| Python Programming |
duckdb.sql(" SELECT COUNT(*) as Allstars FROM emp WHERE comm > 0 ").df()
| Results |
Allstars
0 3
2) 분석 함수
| Python Programming |
%%sql
SELECT ename, sal,
COUNT(*) OVER (ORDER BY sal RANGE BETWEEN 50 PRECEDING AND
150 FOLLOWING) AS mov_count
FROM emp
ORDER BY sal, ename;
| Python Programming |
duckdb.sql(" SELECT ename, sal, \
COUNT(*) OVER (ORDER BY sal RANGE BETWEEN 50 PRECEDING AND \
150 FOLLOWING) AS mov_count \
FROM emp \
ORDER BY sal, ename \
LIMIT 6 ").df()
| Results |
ename sal mov_count
0 SMITH 800 2
1 JAMES 950 2
2 ADAMS 1100 3
3 MARTIN 1250 3
4 WARD 1250 3
5 MILLER 1300 3
[참고] ROWS BETWEEN를 사용하여서 현재 sal의 값을 기준으로 x 이전 관측치 ~ x 이후 2개의 관측치의 개수를 카운트
| Python Programming |
%%sql
SELECT a.*,
COUNT(*) OVER (ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS PRE_FOL_CNT
FROM emp a
ORDER BY SAL
| Python Programming |
SELECT a.empno, a.ename, a.job, a.sal, \
COUNT(*) OVER (ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS PRE_FOL_CNT \
FROM emp a \
ORDER BY SAL \
LIMIT 6 ").df()
| Results |
empno ename job sal PRE_FOL_CNT
0 7369 SMITH CLERK 800 3
1 7900 JAMES CLERK 950 4
2 7876 ADAMS CLERK 1100 4
3 7521 WARD SALESMAN 1250 4
4 7654 MARTIN SALESMAN 1250 4
5 7934 MILLER CLERK 1300 4
3) 비등가 조인
| Python Programming |
%%sql
SELECT A.ENAME,A.SAL,B.mov_count
FROM emp A
LEFT JOIN ( SELECT a.sal,count(distinct b.empno) mov_count
FROM emp a, emp b
where b.sal between a.sal - 50 and a.sal + 150
group by a.sal ) B
ON A.SAL = B.SAL
ORDER BY 2,1
| Python Programming |
duckdb.sql(" SELECT A.ENAME,A.SAL,B.mov_count \
FROM emp A \
LEFT JOIN ( SELECT a.sal,count(distinct b.empno) mov_count \
FROM emp a, emp b \
where b.sal between a.sal - 50 and a.sal + 150 \
group by a.sal ) B \
ON A.SAL = B.SAL \
ORDER BY 2,1 \
LIMIT 6 ").df() )
| Results |
ename sal mov_count
0 SMITH 800 2
1 JAMES 950 2
2 ADAMS 1100 3
3 MARTIN 1250 3
4 WARD 1250 3
5 MILLER 1300 3
| [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table] 오라클 함수와 R & Python 비교 사전 목록? 링크 |
| 오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크 |
| [SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글