发表于 | 分类于

SQL笔试题

下面的SQL基于PostgreSQL

1. 累计值（月累计、年累计）

背景描述

比如说，我们有这样一份数据,记录的是图书每天的销量情况：

日期 图书名称 销量
2017-01-01 解忧杂货店 90
2017-01-03 解忧杂货店 50
2017-01-05 解忧杂货店 100
2017-01-01 雪落香杉树 100
2017-01-03 雪落香杉树 44
2017-01-04 雪落香杉树 99

现在，我们要统计每本书，当月的累计销量？即1号是1号的销量，2号是1号+2号当天的销量（注意：这里2号当天虽然没有销量，但是应该为1号的90+2号的0，为90）。大家先思考下，如果可以很快解答，就不需要接着读啦，有疑问的同学可以继续往下看。

测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 图书的销量表
CREATE TABLE interview.tm_book_sales
(
  calendar_date date, -- 日期
  book_name character varying(100), -- 图书名称
  sales numeric(10,0) -- 销量
)
WITH (
  OIDS=FALSE
);
-- 测试数据
insert into tm_book_sales values('2017-01-01','解忧杂货店',56);
insert into tm_book_sales values('2017-01-02','解忧杂货店',100);
insert into tm_book_sales values('2017-01-03','解忧杂货店',70);
insert into tm_book_sales values('2017-01-06','解忧杂货店',11);
insert into tm_book_sales values('2017-01-07','解忧杂货店',65);
insert into tm_book_sales values('2017-01-08','解忧杂货店',9);
insert into tm_book_sales values('2017-01-09','解忧杂货店',30);
insert into tm_book_sales values('2017-01-10','解忧杂货店',56);
insert into tm_book_sales values('2017-01-01','雪落香杉树',18);
insert into tm_book_sales values('2017-01-02','雪落香杉树',40);
insert into tm_book_sales values('2017-01-03','雪落香杉树',2);
insert into tm_book_sales values('2017-01-04','雪落香杉树',22);
insert into tm_book_sales values('2017-01-05','雪落香杉树',48);
insert into tm_book_sales values('2017-01-07','雪落香杉树',71);
insert into tm_book_sales values('2017-01-09','雪落香杉树',73);
insert into tm_book_sales values('2017-01-10','雪落香杉树',37);
insert into tm_book_sales values('2017-02-03','解忧杂货店',5);
insert into tm_book_sales values('2017-02-05','解忧杂货店',46);
insert into tm_book_sales values('2017-02-06','解忧杂货店',35);
insert into tm_book_sales values('2017-02-07','解忧杂货店',10);
insert into tm_book_sales values('2017-02-09','解忧杂货店',30);
insert into tm_book_sales values('2017-02-10','解忧杂货店',12);
insert into tm_book_sales values('2017-02-13','解忧杂货店',43);
insert into tm_book_sales values('2017-02-01','雪落香杉树',10);
insert into tm_book_sales values('2017-02-04','雪落香杉树',78);
insert into tm_book_sales values('2017-02-10','雪落香杉树',50);
insert into tm_book_sales values('2017-02-20','雪落香杉树',93);

现在呢，我们有了图书每天的销量数据，下面，我们思考1个问题：
我想要统计每本图书的当月累计销量，应该怎么做呢？

阅读全文 »

发表于 | 分类于

这里整理下时间类函数的使用方法，主要是整理下，防止老是忘记。
参考官方网站：
https://docs.python.org/3/library/datetime.html?highlight=strftime#strftime-strptime-behavior

1. strftime

datetime.strftime(format)Return a string representing the date and time, controlled by an explicit format string.返回将datetime根据指定format格式化后的字符串

1
2
3
4
5
6
7
8
9
10
11
12
from datetime import datetime
now = datetime.now()
now
Out[3]: datetime.datetime(2017, 8, 23, 10, 21, 41, 802905)
now.strftime('%Y-%m-%d %H:%M:%S')
Out[5]: '2017-08-23 10:21:41'
 now.strftime('%Y-%m-%d %H:%M:%S %f')
Out[6]: '2017-08-23 10:21:41 802905'

这里主要是了解format格式就可以了
常用格式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
参考官方网站：http://www.runoob.com/python/att-time-strftime.html
    %y 两位数的年份表示（00-99）
    %Y 四位数的年份表示（000-9999）
    %m 月份（01-12）
    %d 月内中的一天（0-31）
    %H 24小时制小时数（0-23）
    %I 12小时制小时数（01-12）
    %M 分钟数（00=59）
    %S 秒（00-59）
    %a 本地简化星期名称
    %A 本地完整星期名称
    %b 本地简化的月份名称
    %B 本地完整的月份名称
    %c 本地相应的日期表示和时间表示
    %j 年内的一天（001-366）
    %p 本地A.M.或P.M.的等价符
    %U 一年中的星期数（00-53）星期天为星期的开始
    %w 星期（0-6），星期天为星期的开始
    %W 一年中的星期数（00-53）星期一为星期的开始
    %x 本地相应的日期表示
    %X 本地相应的时间表示
    %Z 当前时区的名称
    %% %号本身

2. strptime

datetime.strptime(date_string, format)
Return a datetime corresponding to date_string, parsed according to format
根据format，将字符串转换为datetime

1
2
3
4
datetime.strptime('2017-08-23 10:21:41 802905','%Y-%m-%d %H:%M:%S %f')
Out[8]: datetime.datetime(2017, 8, 23, 10, 21, 41, 802905)
#就是strftime的一个逆过程，一个是日期到字符串，一个是字符串到日期

发表于 | 分类于

下午看了社区里的一篇文章《Python 爬虫实践：《战狼2》豆瓣影评分析》，感谢分享。
最近也是在学习爬虫，周末刚好看了词云图，这里就自己也来实现下。

周末的词云图介绍《word_cloud-用Python之作个性化词云图》

豆瓣影评页面分析

我们到豆瓣电影模块，选择《战狼2》，找到下面的短评

页面地址：https://movie.douban.com/subject/26363254/comments?status=P

通过FireBug，观察页面，可以发现，评论信息还是很好拿的

然后，我们看看下一页数据是怎么获取的

这里是直接用参数传的，多点几次观察，就会发现规律

阅读全文 »

发表于 | 分类于

周末没事，想看看最近的职位招聘情况，就用拉勾网为例，练习下爬虫

分析

首先去拉勾网的主页去观察下，
当前的搜索页面URL：

观察下源码，发现结构很清晰，解析起来应该很方便，再通过下一页去遍历即可

通过上面的，我们直接去解析应该是足够了，我们再看下他的请求信息，会找到这样一个post，
很明显，这个pn应该就是页码（多查询几次观察下可以确认）

这个应该就是查询的请求，看看结果集，就是我们想要的数据

所以，如果我们直接去请求json数据，然后解析下就行了

要注意的小问题

写的时候，主要遇到几个问题，

  • 2.1 直接请求的话，不行，会被拦截，关于拉勾网，网上的例子很多，这里测试的时候，只要加上header就行了，其他的不需要
    headers = {‘User-Agent’:r’Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:55.0) Gecko/20100101 Firefox/55.0’,’Referer’:r’https://www.lagou.com/jobs/list_%E6%95%B0%E6%8D%AE%E5%88%86%E6%9E%90?labelWords=&fromSearch=true&suginput','Host':'www.lagou.com'}

  • 2.2 json的结果集中，有总条数和每页条数，根据这2点，我们可以算出总页数

  • 2.3 解析json就行了
  • 2.4 在 循环的时候，sleep了5秒，因为一直查询偶尔会报错，估计是被拦截了

代码

下面分享下，第一版代码，后面再优化：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
# -*- coding: utf-8 -*-
"""
Created on Sat Aug 19 15:22:20 2017
@author: yuguiyang
"""
import urllib
import json
import math
import csv
import time
import os
total_page = 1
current_page = 1
#更新结果集的总页数
def update_total_page(page_size,totalCount):
    global total_page
    
    total_page = math.ceil(totalCount/page_size)
    
#解析HTML页面
def parse_html(url,headers,post_data):
    post_data = urllib.parse.urlencode(post_data).encode('utf-8')
    req = urllib.request.Request(url,headers=headers,data=post_data)
    page = urllib.request.urlopen(req)
    
    return page.read().decode('utf-8')
    
#解析返回的json数据
def parse_result(html):
    data_json = json.loads(html)
    print(data_json['content']['pageNo'])
    
    #第一页的话，更新下总页数
    if data_json['content']['pageNo'] == 1 :
        update_total_page(data_json['content']['pageSize'],data_json['content']['positionResult']['totalCount'])
    #将结果保存到csv文件
    with open('lagou_shujufenxi_data.csv','a',newline='',encoding='utf-8') as csvfile:
        csv_writer = csv.writer(csvfile,quotechar='"',quoting=csv.QUOTE_MINIMAL)
        #在第一页的时候，写入标题
        if data_json['content']['pageNo'] == 1 :
            csv_writer.writerow(['公司简称','公司全称','所属行业','工作地点','职位特点','薪资','经验','职位名称','firstType','secondType'])
        
        #遍历招聘信息
        for job in data_json['content']['positionResult']['result']:
            line = []
            line.append(job['companyShortName'])
            line.append(job['companyFullName'])
            line.append(job['industryField'])
            line.append(job['district'])
            line.append(job['positionAdvantage'])
            line.append(job['salary'])
            line.append(job['workYear'])
            line.append(job['positionName'])
            line.append(job['firstType'])
            line.append(job['secondType'])
            
            csv_writer.writerow(line)
def main():
    headers = {
            'User-Agent':r'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:55.0) Gecko/20100101 Firefox/55.0',
            'Referer':r'https://www.lagou.com/jobs/list_%E6%95%B0%E6%8D%AE%E5%88%86%E6%9E%90?labelWords=&fromSearch=true&suginput',
            'Host':'www.lagou.com'
            }
            
    url = 'https://www.lagou.com/jobs/positionAjax.json'
    
    #判断要导出的文件是否存在
    if os.path.exists(r'lagou_shujufenxi_data.csv'):
        print('hey,delete the file')
        os.remove(r'lagou_shujufenxi_data.csv')
    
    #循环所有页数，取导出数据    
    while current_page <= total_page:
        #post参数,每次修改页码就行了
        post_data = {'city':'上海','isSchoolJob':'0','needAddtionalResult':'false','kd':'数据分析','pn':current_page}
        #解析每一个页面
        html = parse_html(url ,headers, post_data)
        #处理获取的json数据
        parse_result(html)
        
        #当前页码加1
        current_page += 1
        
        #这里sleep5秒，不停一下，偶尔会报错
        time.sleep(5)
    
if __name__=='__main__':
    main()

发表于 | 分类于

今天想复习下BeautifulSoup，就把之前的代码拿过来测试，发现报错了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import urllib
from bs4 import BeautifulSoup
#加载网址，获取当前页面
def getHTML(url) :
    page = urllib.urlopen(url)
    html = page.read()
    return html
html = getHTML('https://movie.douban.com/top250')
soup = BeautifulSoup(html, "html.parser")
for img in soup.find_all('img'):
    print(img.get('src'))

查了下，发下是Python3中，需要引入的模块变了
改一下就可以了

1
urllib.request.urlopen(url)