这是我写的
因为英文太烂，写不了英文注释，说以在这补一下这个脚本的功能说明：这个脚本是统计书籍的订购量，订购用户数，浏览量等信息（以天为单位），这里的书籍，是按章销售的，所以记录的最小粒度是章节的订购量，订购用户，那么脚本就需要根据bookId为唯一标识，去累加各章（在某一天的）的数据，汇总到一本书下。基本流程是：
- 清理上次计算产生的临时文件
- 下载最新的数据到临时文件
- 统计 并将结果生成SQL脚本
- 执行SQL脚本入库
其中统计又分为
- 3.1从临时文件创建一个books数组（他是一个复合数据结构，每本书的属性都可以通过books[id,"属性"]索引）
- 3.2遍历章节销售记录，根据记录里的bookId写入对应的数据结构里
- 3.3生成SQL语句
user@server:/work/stat/read/orderdetail> cat orderdetail.sh
1 #!/bin/sh 2 3 ############################################### 4 # # 5 # author:lishujun # 6 # date:2013-4-11 # 7 # use:$orderdetail 2013-4-11 # 8 # # 9 ############################################### 10 11 ### load Library ### 12 13 . /www/log/stat/db_alias 14 . /www/log/stat/public_func 15 init_date $1 16 init_db 17 18 19 ### define global variable ### 20 21 bookStatTable='bookOrder' 22 bookNameFile='./tmp/book_name_2013' 23 orderDetailFile='./tmp/order_detail_2013' 24 cpBookFile='./tmp/cp_book' 25 clickedNumberFile='./tmp/click_number' 26 tempDataFile='./tmp/data' 27 scriptFile='./tmp/sql_script' 28 29 30 ### functions ### 31 32 cleanTemporaryFiles() 33 { 34 echo clean temporary files... 35 rm $bookNameFile 36 rm $orderDetailFile 37 rm $cpBookFile 38 rm $clickedNumberFile 39 rm $tempDataFile 40 rm $scriptFile 41 } 42 43 executeSql() 44 { 45 echo writing to database... 46 cat $scriptFile | statdb -f 47 } 48 49 loadDataToFiles() 50 { 51 echo load data... 52 #load basic info : bookId, bookName, author, class 53 echo "select id,bookname,author,class from bc_bookinfos" |bookdb -s|piconv -f utf8 -t gb2312 >$bookNameFile 54 55 #load cp books: cpId, bookId 56 echo "select cpid,bid from bc_book_charge_cp_bid" |bookdb -s|piconv -f utf8 -t gb2312 >$cpBookFile 57 58 #load book click number 59 echo "select id,num_today_click from bc_bookinfos_ext_stat" |bookdb -s|piconv -f utf8 -t gb2312 >$clickedNumberFile 60 61 #load order info : userId, bookId, chapId, orderAmount, size 62 for i in `echo "show tables like 'bc_user_charged_list_wap%';"|booknewdb -s` 63 do 64 sql="select userid,bid,chapterid,price,size,from_unixtime(charged_time) from $i" 65 sql=$sql" where from_unixtime(charged_time) like '$stat_date%' " 66 67 echo $sql | booknewdb -s 68 #echo $sql 69 done >$orderDetailFile 70 71 } 72 73 stat() 74 { 75 echo stat... 76 awk -v statDate=$stat_date \ 77 -v bookStatTable=$bookStatTable \ 78 -v bookNameFile=$bookNameFile \ 79 -v cpBookFile=$cpBookFile \ 80 -v clickedNumberFile=$clickedNumberFile \ 81 -v tempDataFile=$tempDataFile \ 82 -v scriptFile=$scriptFile \ 83 ' 84 ### util functions ### 85 86 function isVaild(value) 87 { 88 if(value > 0) 89 return 1 90 else 91 return 0 92 } 93 94 function computeARPU(bookId,books) 95 { 96 if(isVaild(books[bookId,"orderUserCount"]) == 0) 97 { 98 return 0 99 } 100 101 return books[bookId,"orderAmount"] / books[bookId,"orderUserCount"] 102 } 103 104 105 function readerConversionRate(bookId,books) 106 { 107 108 if(isVaild(books[bookId,"clickedUserCount"]) == 0) 109 { 110 return 0 111 } 112 113 return books[bookId,"readerCount"] / books[bookId,"clickedUserCount"] 114 } 115 116 117 function deepReaderConversionRate(bookId,books) 118 { 119 if(isVaild(books[bookId,"readerCount"]) == 0) 120 { 121 return 0 122 } 123 124 return books[bookId,"deepReaderCount"] / books[bookId,"readerCount"] 125 } 126 127 function readerOrderRate(bookId,books) 128 { 129 if(isVaild(books[bookId,"readerCount"]) == 0) 130 { 131 return 0 132 } 133 134 retunrn books[bookId,"orderUserCount"] / books[bookId,"readerCount"] 135 } 136 137 function browserOrderRate(bookId,books) 138 { 139 if(isVaild(books[bookId,"clickedUserCount"]) == 0) 140 { 141 return 0 142 } 143 144 retunrn books[bookId,"orderUserCount"] / books[bookId,"clickedUserCount"] 145 } 146 147 function getSubArrayNames() 148 { 149 names = "authorId,classId,cpId,statDate,orderAmount,orderCount,orderUserCount," 150 names = names "readerCount,deepReaderCount,clickedUserCount," 151 names = names "ARPU,readerConversionRate,deepReaderConversionRate,readerOrderRate,browserOrderRate" 152 153 return names 154 } 155 156 function makeDeleteSql(bookId) 157 { 158 sql = "delete from "bookStatTable" where bookId=" bookId " and statDate=\"" statDate "\";" 159 print sql >> scriptFile 160 } 161 162 function makeInsertSql(bookId,books) 163 { 164 names = getSubArrayNames() 165 split(names,nameArray,",") 166 167 filedNames = "" 168 filedValues = "" 169 170 for(i in nameArray) 171 { 172 filedNames = filedNames""nameArray[i]"," 173 filedValues = filedValues"\""books[bookId,nameArray[i]]"\"," 174 } 175 176 #filedNames = substr(filedNames,0,length(filedNames)-1) 177 #filedValues = substr(filedValues,0,length(filedValues)-1) 178 179 filedNames = filedNames"bookId" 180 filedValues = filedValues""bookId 181 182 sql = "insert into " bookStatTable "(" filedNames ") values(" filedValues ");" 183 print sql >> scriptFile 184 } 185 186 function printBookStatInfo(bookId,books) 187 { 188 names = getSubArrayNames() 189 split(names,nameArray,",") 190 191 printf("bookId=%s,detail:\n",bookId) >> tempDataFile 192 for(i in nameArray) 193 { 194 printf("%s is %s\n",nameArray[i],books[bookId,nameArray[i]]) >> tempDataFile 195 } 196 } 197 198 199 ### make books ### 200 BEGIN{ 201 #load cpId 202 while(getline<cpBookFile == 1) 203 { 204 cpId=$1 205 bookId=$2 206 cpBook[bookId]=cpId 207 } 208 209 #load clicked stat 210 while(getline<clickedNumberFile == 1) 211 { 212 bookId=$1 213 clickedNumber=$2 214 clickedStatList[bookId]=clickedNumber 215 } 216 217 #define books 218 while(getline<bookNameFile == 1) 219 { 220 bookId = $1 221 bookName = $2 222 authorId = $3 223 classId = $4 224 225 #initialize basic info of this book 226 indexSet[bookId] = bookId 227 books[bookId,"authorId"] = authorId 228 books[bookId,"classId"] = classId 229 books[bookId,"cpId"] = cpBook[bookId] 230 books[bookId,"statDate"] = statDate 231 232 #initialize browse info of this book 233 books[bookId,"readerCount"] = 0 234 books[bookId,"deepReaderCount"] = 0 235 books[bookId,"clickedUserCount"] = clickedStatList[bookId] 236 237 #initialize order info of this book 238 books[bookId,"orderAmount"] = 0.0 239 books[bookId,"orderUserCount"] = 0 240 books[bookId,"orderCount"] = 0 241 242 #initialize ARPU and Rates 243 books[bookId,"ARPU"] = 0 244 books[bookId,"readerConversionRate"] = 0 245 books[bookId,"deepReaderConversionRate"] = 0 246 books[bookId,"readerOrderRate"] = 0 247 books[bookId,"browserOrderRate"] = 0 248 } 249 } 250 251 252 ### compute OrderCost ### 253 { 254 userId=$1 255 bookId=$2 256 chapId=$3 257 price=$4 258 size=$5 259 260 if(bookId in indexSet) 261 { 262 books[bookId,"orderAmount"] += price 263 books[bookId,"orderUserCount"] += 1 264 books[bookId,"orderCount"] += 1 265 } 266 } 267 268 269 ### make SQL statement ### 270 END{ 271 print "END..." 272 for (bookId in indexSet) 273 { 274 275 print "bookId in indexSet" 276 if (books[bookId,"orderCount"]>0) 277 { 278 #compute ARPU and Rates 279 books[bookId,"ARPU"] = computeARPU(bookId,books) 280 books[bookId,"readerConversionRate"] = readerConversionRate(bookId,books) 281 books[bookId,"deepReaderConversionRate"] = deepReaderConversionRate(bookId,books) 282 books[bookId,"readerOrderRate"] = readerOrderRate(bookId,books) 283 books[bookId,"browserOrderRate"] = browserOrderRate(bookId,books) 284 285 print "orderCount > 0" 286 #printBookStatInfo(bookId,books) 287 #makeDeleteSql(bookId) 288 makeInsertSql(bookId,books) 289 } 290 } 291 } 292 ' $orderDetailFile 293 } 294 295 296 main() 297 { 298 cleanTemporaryFiles 299 loadDataToFiles 300 stat 301 executeSql 302 echo done 303 } 304 305 #call main function 306 main
