看图写文字的任务这篇就不做了，这里我们只解决需求

1.1、APP类型数量

SELECT count(DISTINCT prime_genre) as APP类型数量 
from appstore;

1.2、每种类型APP数量

按类型分组并计数，降序排序
SELECT prime_genre,count(prime_genre) as total_num
from appstore
GROUP BY prime_genre
ORDER BY total_num desc;

共23个APP类型，以及各自类目下数量

2、评价最多的APP类型，总评价数量、平均评价数量。

SELECT prime_genre,sum(rating_count_tot) total_ratings,AVG(rating_count_tot) avg_ratings
from appstore
GROUP BY prime_genre
ORDER BY total_ratings desc,avg_ratings desc;

3、各类型评价数目最大的APP？

应该是用子查询，当该类型下该APP评价数大于等于该类型下所有其他APP评价数时

SELECT id,track_name,prime_genre,rating_count_tot
from appstore a
where  rating_count_tot >= all(
select rating_count_tot from appstore b where a.prime_genre = b.prime_genre
)
ORDER BY rating_count_tot desc;

4、各价位各类型APP的占比情况

SELECT prime_genre,
sum(case when price=0 then 1 else 0 end)/count(prime_genre) as '0',
sum(case when price<=1 then 1 else 0 end)/count(prime_genre) as '0-1',
sum(case when price>1 and price<=2 then 1 else 0 end)/count(prime_genre) as '1-2',
sum(case when price>2 and price<=3 then 1 else 0 end)/count(prime_genre) as '2-3',
sum(case when price>3 and price<=4 then 1 else 0 end)/count(prime_genre) as '3-4',
sum(case when price>4 and price<=5 then 1 else 0 end)/count(prime_genre) as '4-5',
sum(case when price>5 and price<=10 then 1 else 0 end)/count(prime_genre) as '5-10',
sum(case when price>10 then 1 else 0 end)/count(prime_genre) as '10'
from appstore
GROUP BY prime_genre;

各价位APP占比情况

各价位各类型APP的占比情况

5、找出Games、Health & Fitness、Social networking、Utilities、Productivity这5种类型中评论数在前三的APP

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

(SELECT track_name ,prime_genre,rating_count_tot
from appstore
where prime_genre='Games'
order by rating_count_tot desc
limit 3)
union all
(SELECT track_name ,prime_genre,rating_count_tot
from appstore
where prime_genre='Health & Fitness'
order by rating_count_tot desc
limit 3)
union all
(SELECT track_name ,prime_genre,rating_count_tot
from appstore
where prime_genre='Social networking'
order by rating_count_tot desc
limit 3)
union all
(SELECT track_name ,prime_genre,rating_count_tot
from appstore
where prime_genre='Utilities'
order by rating_count_tot desc
limit 3)
union all
(SELECT track_name ,prime_genre,rating_count_tot
from appstore
where prime_genre='Productivity'
order by rating_count_tot desc
limit 3);

6、鹅厂的APP表现如何

select track_name,rating_count_tot,user_rating
from appstore
where track_name like "%QQ%" or track_name like "%wechat%" or track_name like "%腾讯%"
order by rating_count_tot desc ,user_rating desc;

微信APP位居榜首，总评价数远高于其他APP，达到34000，评分4.5，无论是热度还是好评度都是最高的；QQ和QQHD的评价数都有5000以上，但是评分略低；腾讯视频与QQ浏览器的评价数分别为2958、1750，其他APP评论数不够一千，其市场热度还有待挖掘。

7、直播类APP有哪些

select track_name,rating_count_tot,user_rating
from appstore
where track_name like "%直播%" or track_name like "%Live%"
order by rating_count_tot desc ,user_rating desc;