insert overwrite table common.order
select
 userid
 ,case 
 when hour_time>=0 and hour_time<=2 then '00_03'
 when hour_time>=3 and hour_time<=5 then '03_06' 
 when hour_time>=6 and hour_time<=7 then '06_08'
 when hour_time>=8 and hour_time<=11 then '08_12'
 when hour_time>=12 and hour_time<=13 then '12_14'
 when hour_time>=14 and hour_time<=17 then '14_18'
 when hour_time>=18 and hour_time<=23 then '18_24'
 else '0'
 end 
 as hour_time 
 ,count(distinct dt) hour_dt_num
 where dt >='2014-10-01'
 and dt<='2014-10-30'
 group by userid,
 case when hour_time>=0 and hour_time<=2 then '00_03'
 when hour_time>=3 and hour_time<=5 then '03_06' 
 when hour_time>=6 and hour_time<=7 then '06_08'
 when hour_time>=8 and hour_time<=11 then '08_12'
 when hour_time>=12 and hour_time<=13 then '12_14'
 when hour_time>=14 and hour_time<=17 then '14_18'
 when hour_time>=18 and hour_time<=23 then '18_24'
 else '0'
 end 

#!/bin/bash
#
# add by lishc 2014-11-25
 
mysql=`which mysql`
user="root"
password="123"
database="test"
table="parm"
 
command="select par1,par2,id from test.$table "
$mysql -u${user} -p${password} -e "${command}" >m.txt
 
###初始化
echo " insert overwrite table common.order">mysql.sql
echo " select ">>mysql.sql
echo " userId " >>mysql.sql
echo " case ">>mysql.sql
 
sed -i -e '1d' m.txt
cat m.txt |while read line 
do
 par1=$(echo "${line}"|awk -F ' ' '{print $1}')
 par2=$(echo "${line}"|awk -F ' ' '{print $2}') 
 id=$(echo "${line}"|awk -F ' ' '{print $3}') 
 echo "par1: ${par1}"
 echo "par2: ${par2}"
 echo " when hour_time >=${par1} and hour_time<=${par2} then '${id}' ">>mysql.sql
Done