def run(self):
print "Starting " + self.name
while not exitFlag:
queueLock.acquire()
if not workQueue.empty():
action, delta = self.q.get()
queueLock.release()
COMMAND = "DROP TABLE IF EXISTS " + action + "_count" + str(
delta)
safeExecute(COMMAND, self.con, self.cur, workQueue)
COMMAND = """
CREATE TABLE """ + action + """_count""" + str(delta) + """
SELECT W.date AS time,
W.id AS id,
COUNT(W.id) AS count,
W.churned AS churned
FROM WCU3 AS W
LEFT JOIN """ + action + """ AS e
ON W.id = e.id
AND DATE(e.time) < W.date"""
if delta > 0:
COMMAND += " AND DATE(e.time) >= W.date - INTERVAL " + str(
delta) + " day"
COMMAND += """
GROUP BY 1,2
ORDER BY 1
"""
print self.name + " Executing:" + COMMAND
safeExecute(COMMAND, self.con, self.cur, workQueue)
else:
queueLock.release()
time.sleep(1)
print "Exiting " + self.name
def run(self):
print "Starting " + self.name
while not exitFlag:
queueLock.acquire()
if not workQueue.empty():
action = self.q.get()[0]
queueLock.release()
COMMAND = "DROP TABLE IF EXISTS "+action+"_last"
safeExecute(COMMAND,self.con,self.cur,workQueue)
COMMAND = """
CREATE TABLE """+action+"""_last
SELECT W.date AS time,
W.id AS id,
CASE
WHEN Max(e.time) IS NOT NULL THEN Max(e.time)
ELSE DateDiff(e.time,W.minTime)
END AS last,
CASE
WHEN Max(e.time) IS NOT NULL THEN 1
ELSE 0
END AS has_done
,W.churned
FROM WCU3 AS W
LEFT JOIN """+action+""" AS e
ON W.id = e.id
AND DATE(e.time) < W.date
GROUP BY 1,2
ORDER BY 1
"""
print self.name+" Executing:"+COMMAND
safeExecute(COMMAND,self.con,self.cur,workQueue)
else:
queueLock.release()
time.sleep(1)
print "Exiting " + self.name
def run(self):
print "Starting " + self.name
while not exitFlag:
queueLock.acquire()
if not workQueue.empty():
code, day = self.q.get()
queueLock.release()
#Create reduced activity table
COMMAND = "DROP TABLE IF EXISTS Act_"+str(code)
safeExecute(COMMAND,self.con,self.cur,workQueue)
COMMAND = """
CREATE TABLE Act_"""+str(code)+"""
SELECT day, id, plan
FROM ActiveUsers
WHERE id in(
SELECT id FROM ActiveUsers
WHERE day ='"""+str(day)+"""')"""
safeExecute(COMMAND,self.con,self.cur,workQueue)
COMMAND = """ALTER TABLE Act_"""+str(code)+""" ADD KEY (day)"""
print(COMMAND)
with self.con:
self.cur.execute(COMMAND)
COMMAND = """ALTER TABLE Act_"""+str(code)+""" ADD KEY (id)"""
print(COMMAND)
with self.con:
self.cur.execute(COMMAND)
COMMAND = """ALTER TABLE Act_"""+str(code)+""" ADD KEY (id,day)"""
print(COMMAND)
with self.con:
self.cur.execute(COMMAND)
COMMAND = """ALTER TABLE Act_"""+str(code)+""" ADD KEY(day,id)"""
print(COMMAND)
with self.con:
self.cur.execute(COMMAND)
#Now the complicated query on a smaller table
COMMAND ="DROP TABLE IF EXISTS WCU3_"+str(code)
safeExecute(COMMAND,self.con,self.cur,workQueue)
COMMAND ="""
CREATE TABLE WCU3_"""+str(code)+"""
SELECT
'"""+str(day)+"""' AS date ,
temp.e_id AS id ,
MIN(temp.eTime) AS minTime,
CASE
WHEN temp.ee_id IS NOT NULL THEN 0
ELSE 1
END AS 'churned',
temp.plan AS plan
FROM
(
SELECT
e.id AS e_id,
ee.id AS ee_id,
e.day AS eTime,
ee.day AS eeTime,
e.plan AS plan
FROM Act_"""+str(code)+""" e
LEFT JOIN Act_"""+str(code)+""" ee
ON e.id = ee.id
AND ee.day > '"""+str(day)+"""'
AND ee.day < '"""+str(day)+"""' + INTERVAL 28 day
) AS temp
GROUP BY 1,2
ORDER BY 1
"""
print self.name+" Executing:"+COMMAND
safeExecute(COMMAND,self.con,self.cur,workQueue)
COMMAND = """ALTER TABLE WCU3_"""+str(code)+""" ADD KEY (date)"""
print(COMMAND)
safeExecute(COMMAND,self.con,self.cur,workQueue)
COMMAND = """ALTER TABLE WCU3_"""+str(code)+""" ADD KEY (id)"""
print(COMMAND)
safeExecute(COMMAND,self.con,self.cur,workQueue)
COMMAND = """ALTER TABLE WCU3_"""+str(code)+""" ADD KEY (id,date)"""
print(COMMAND)
safeExecute(COMMAND,self.con,self.cur,workQueue)
COMMAND = """ALTER TABLE WCU3_"""+str(code)+""" ADD KEY (date,id)"""
print(COMMAND)
safeExecute(COMMAND,self.con,self.cur,workQueue)
else:
queueLock.release()
time.sleep(1)
print "Exiting " + self.name
(FROM WCU3_'"""+str(today)""",
SELECT date, id, minTime, churned, plan
"""
today = today + datetime.timedelta(days=1)
while (today < end):
today = today + datetime.timedelta(days=1)
COMMAND +="""UNION
FROM WCU3_'"""+str(today)""",
SELECT date, id, minTime, churned, plan
"""
COMMAN += """) temp; """
with con:
cur.execute(COMMAND)
print self.name+" Executing:"+COMMAND
safeExecute(COMMAND,self.con,self.cur,workQueue)
COMMAND = """ALTER TABLE WCU3"""+str(code)+""" ADD KEY (date)"""
print(COMMAND)
safeExecute(COMMAND,self.con,self.cur,workQueue)
COMMAND = """ALTER TABLE WCU3"""+str(code)+""" ADD KEY (id)"""
print(COMMAND)
safeExecute(COMMAND,self.con,self.cur,workQueue)
COMMAND = """ALTER TABLE WCU3"""+str(code)+""" ADD KEY (id,date)"""
print(COMMAND)
safeExecute(COMMAND,self.con,self.cur,workQueue)
COMMAND = """ALTER TABLE WCU3"""+str(code)+""" ADD KEY (date,id)"""
print(COMMAND)