06sql

[leechiron]

SELECT t3.shop_id, t3.shop_name, sum(r.totsnu) tsnu, sum(r.totsmo) tsmo, sum(r.quantity) trenu, sum(r.restmo) tremo FROM ( SELECT t1.item_id, t1.totsnu, t1.totsmo, t2.quantity, t2.restmo FROM (SELECT item_id, sum(day_sold) totsnu, sum(daysmo) totsmo FROM (SELECT item_id, day_sold, day_sold_price daysmo FROM t_base_ec_item_daysale_dev_new) t GROUP BY item_id) t1 JOIN (SELECT item_id, qu quantity, cast(price * qu AS FLOAT) restmo FROM t_base_ec_item_sold_dev WHERE ds = '20160528') t2 ON t1.item_id = t2.item_id ) r JOIN (SELECT y1.item_id, y1.shop_id, y2.shop_name FROM (SELECT item_id, shop_id FROM t_base_ec_item_dev_new WHERE ds = '20160607' AND bc_type = 'B') y1 JOIN (SELECT shop_id, shop_name FROM t_base_ec_shop_dev WHERE ds = '20160613' ) y2 ON y1.shop_id = y2.shop_id ) t3 ON r.item_id = t3.item_id GROUP BY shop_id,shop_name

[leechiron]

-- coding: utf-8 --

author = 'leechiron'

this is python 2.76

a bug is on this. !!!!!!!!!!!!!!!

as"since it exceeds Excel's limit of 65,530 URLS per worksheet"

but my sheet saves 242k lines,58MB in csv,20MB in xlsx.

locat1='/home/leechiron/item.res.20160613-20160614.csv' locat2='/home/leechiron/item.res.20160613-20160614.xlsx'

import xlsxwriter import csv import json

csvtoxlsx

def xlsxuse (inl,outl): workbook=xlsxwriter.Workbook(outl) worksheet=workbook.add_worksheet() inf=open(inl,'rb') outf=open(outl,'wb') bold=workbook.add_format({'bold':True}) sheetcolumsign=1 #表头标记 sheetcolumnum=0 #表头列数统计 linenumcount=0 #每行 写入的列数 0开始 sheetlinenum=0 #写入的行数 0开始

reader=csv.reader(inf)
#excelwriter=csv.writer(outf,dialect='excel')
for row in reader:
    #excelwriter.writerow(row)
    if row!=[]:
        if sheetcolumsign==1 :
            sheetcolumnum=len(row)
            linenumcount=0
            for linenumcount in range(0,sheetcolumnum):
                try:
                    utfone= row[linenumcount].decode('utf-8')
                except Exception,e:
                    utfone=row[linenumcount]
                worksheet.write(sheetlinenum,linenumcount,utfone,bold)

            sheetcolumsign=0
            sheetlinenum+=1
        else:
            linenumcount=0
            for linenumcount in range(0,sheetcolumnum):
                try:
                    utfone=str(row[linenumcount].decode('utf-8'))
                except Exception,e:
                    utfone=str(row[linenumcount])
                worksheet.write(sheetlinenum,linenumcount,utfone)
            sheetlinenum+=1
    else:
        pass
inf.close()
outf.close()
workbook.close()
print 'ok'

xlsxuse(locat1,locat2)

[leechiron]

shop statistics

# SELECT u1._, u2.credit_loc_rk, u2.total_locatrn FROM ( SELECT y1., y2.total_maincat_rn FROM (SELECT t1. FROM ( SELECT main_cat_name, shop_id, shop_name, ROW_NUMBER() OVER (PARTITION BY main_cat_name ORDER BY desc_score DESC) AS desc_score_rk, desc_score, ROW_NUMBER() OVER (PARTITION BY main_cat_name ORDER BY service_score DESC) AS service_score_rk, service_score, ROW_NUMBER() OVER (PARTITION BY main_cat_name ORDER BY wuliu_score DESC) AS wuliu_score_rk, wuliu_score, ROW_NUMBER() OVER (PARTITION BY main_cat_name ORDER BY credit DESC) AS credit_maincat_rk, credit, ROW_NUMBER() OVER (PARTITION BY main_cat_name ORDER BY fans_count DESC) AS fans_count_rk, fans_count, ROW_NUMBER() OVER (PARTITION BY main_cat_name ORDER BY shopn DESC) AS shop_mouths_rk, shopn shopmouths FROM (SELECT r., cast((12 * (2017 - YEAR(STARTS)) - MONTH(STARTS)) AS FLOAT) shopn FROM t_zlj_shop_join_major r ) r1 ) t1 ) y1 JOIN (SELECT main_cat_name, COUNT(1) AS total_maincat_rn FROM t_zlj_shop_join_major GROUP BY main_cat_name) y2 ON y1.main_cat_name = y2.main_catname ) u1 JOIN ( SELECT y11., y12.total_rn total_locat_rn FROM (SELECT FROM ( SELECT location, shop_id, shop_name, ROW_NUMBER() OVER (PARTITION BY location ORDER BY credit DESC) AS credit_loc_rk FROM t_zlj_shop_join_major

     ) t11
  ) y11
  JOIN
  (SELECT
     location,
     COUNT(1) AS total_rn
   FROM t_zlj_shop_join_major
   GROUP BY location) y12
    ON y11.location = y12.location

) u2 ON u1.shop_id = u2.shop_id