Hiboboo/CommoditySpider

爬虫

各建表语句：

• 分类表

  CREATE TABLE
  IF
  	NOT EXISTS `classify` (
  		`id` VARCHAR ( 255 ),
  		`pid` VARCHAR ( 255 ),
  		`title` VARCHAR ( 255 ),
  		`url` VARCHAR ( 255 ),
  		`type` VARCHAR ( 55 ),
  		PRIMARY KEY ( `id` ) 
  	) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

• 商品信息表

  CREATE TABLE
  IF
  	NOT EXISTS `commodity` (
  		`id` VARCHAR ( 255 ),
  		`cls_id` VARCHAR ( 255 ),
  		`title` VARCHAR ( 255 ),
  		`mileage` varchar(150),
  		`vendidos` VARCHAR ( 50 ),
  		`month_vendidos` VARCHAR ( 50 ),
  		`price` VARCHAR ( 255 ),
  		`thumbnail` VARCHAR ( 255 ),
  		`show_url` TEXT,
  		PRIMARY KEY ( `id` ) 
  	) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

• 历史订单记录表

  CREATE TABLE
  IF
  	NOT EXISTS `history_order` (
  		`id` VARCHAR ( 255 ),
  		`cid` VARCHAR ( 255 ),
  		`country_name` VARCHAR ( 255 ),
  		`country_code` VARCHAR ( 255 ),
  		`name` VARCHAR ( 255 ),
  		`quantity` VARCHAR ( 255 ),
  		`unit` VARCHAR ( 55 ),
  	PRIMARY KEY ( `id` ) 
  	) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

结果查询

• 查询总商品数量

  SELECT COUNT(id) FROM commodity;

• 查询某个分类下的1000个商品记录

  SELECT DISTINCT
  s.title AS "所属分类",
  c.id AS "编号",
  c.title AS '名称',
  c.vendidos AS '实时销量',
  c.month_vendidos AS '4月总销量',
  concat( 'R$', c.price ) AS '参考价格',
  c.show_url AS '详情链接'
  FROM
  commodity AS c,
  classify AS s 
  WHERE
  c.cls_id = s.id
  AND
  (s.id = '0KRXGL738WY')
  ORDER BY
  c.vendidos + 0 DESC
  LIMIT 1000;

• 历史销量查询（速卖通）待修改验证

  SELECT DISTINCT
  c.cid AS 'ID',
  c.NAME AS '名称',
  c.vendidos AS '实时销量',
  count( o.cid ) * o.quantity AS month_vendidos,
  c.price AS '参考价格',
  c.show_url AS '详情链接' 
  FROM
  commodity AS c,
  history_order AS o,
  classify AS s 
  WHERE
  c.cid = o.cid 
  AND c.cls_id = s.cid 
  AND s.type = 'ali' 
  AND o.country_code = 'br' 
  GROUP BY
  c.cid 
  ORDER BY
  month_vendidos DESC 
  LIMIT 1000