继续介绍如何在R中通过sqldf包执行SQL语句

3.SQLite-JOIN

sqldf包默认以SQLite为后台执行SQL语句。

SQLite支持inner join, left (outer) join, full (outer) join和cross join, 并不支持right join。

sqldf包中的SQLite并没有对full (outer) join提供支持，故我们在sqldf中使用SQLite只支持inner join， left (outer) join和cross join，尽管如此，也能满足我们数据清洗中的大多数要求了。 

先简单介绍一下各种连接。

3.1 inner join--产生两张表（数据框）的交集

df_x

df_y

df_x left join df_y

3.2 cross join

交叉连接的操作，它们都返回被连接的两个表所有数据行的笛卡尔积，返回到的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。在这里跟inner join效果是一样的。

3.3 left join--左连接

产生左边数据框的完全集，而右边中匹配的则有值，没有匹配的则以NA值取代

虽然SQLite不支持right (outer) join，但是可以通过交换表（数据框）的位置用left (outer) join实现右连接的功能。

3.4 group by 分组统计， order by 排序

按gender, year分组统计value均值,并按性别，年份排序。

Example: 根据疾病报卡数据统计每周发病人数

报卡数据样式如下，下面的日期为每个病例的发病日期， 统计每周发病例数（每行为一个病例）

思路大概如下：

1.每一个日期代表一个病例，日期数据可能有缺失， 需要生成一个从开始到结束的日期向量。

2.以从开始到结束的日期向量构成左表，与报卡日期进行左连接， 没匹配上的日期发病数即为0。

3.根据日期生成星期变量， 判断每个星期的结束和开始，并给每个星期顺序编号，以便根据星期编号分组统计发病人数。

具体实现方法可以参考下面的函数：

week_num_f <- function(dates){

    ##dates为报卡的日期向量，一个日期代表一个病例

    df <- data.frame(date = dates)

    df$date <- as.character(ymd(df$date))

    df$dnum <- 1##发病例数， 一行即代表1个病例

    ##根据报卡数据,判断起止日期

    date_max <- max(ymd(df$date))

    date_min <- min(ymd(df$date))

    ##星期天，第一天

    gen_con_dates <- function(date_start, date_end){

        ##生成连续日期向量

        date_start <- ymd(date_start)

        date_end <- ymd(date_end)

        return (as.character((date_start + days(0: (interval(date_start, date_end) / ddays(1))))))

      }

   ##生成从开始到结束的日期数据框

   dates_df <- data.frame(date = gen_con_dates(date_min, date_max))

   dates_df$date <- as.character(dates_df$date)

  ##日期顺序编号

  dates_df$date_num <- 1:nrow(dates_df)

  ##星期编号

  dates_df$wday <- wday(dates_df$date)

  ##年份

  dates_df$year <- year(dates_df$date)

  ##星期按照年份排序

  wnums_by_year <- c(1)

  ##星期连续排序

  wnums_conti <- c(1)

  wnum_conti <- 1

  for (r in 2:nrow(dates_df)){

        ##连续周数

        if (dates_df$wday[r] == 1){##如果是周日， 周数加1

          wnum_conti <- wnum_conti + 1

        }

   wnums_conti <- c(wnums_conti, wnum_conti)

  dates_df$wnum_conti <- wnums_conti

  df <- sqldf('select dates_df.*, df.dnum from

            dates_df left join df on

            dates_df.date == df.date;')

  df$dnum <- ifelse(is.na(df$dnum), 0, df$dnum)##如果日期没有匹配，说明当天没有病例，及病例数为0

  df_wnum_conti <- sqldf('select *, sum(dnum) as count_dnum

                      from df group by wnum_conti;')##按星期分组统计每周发病例数

  df_wnum_conti[, c('date_num', 'dnum', 'wday')] <- NULL

  return (data_wnum_conti = df_wnum_conti)

}