这是我写的

因为英文太烂，写不了英文注释，说以在这补一下这个脚本的功能说明：这个脚本是统计书籍的订购量，订购用户数，浏览量等信息（以天为单位），这里的书籍，是按章销售的，所以记录的最小粒度是章节的订购量，订购用户，那么脚本就需要根据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