数据接口工程对接BI可视化大屏(五)数据接口发布_时序数据库 对接 大屏

@AllArgsConstructor

public class PhoneSalesCompare

{

private String brand;

private Double todaySalesAmount;

private Double yestodaySalesAmount;

}

5.4.2 编写Mapper

@Mapper

@DS(“ck”)

public interface CKMapper

{

//案例三

@Select(“select brand,sum(saleAmount) salesAmount from di where dt = #{dt} group by brand”)

List queryPhoneSalesToday(String dt);

//案例四

@Select("select " +

​ " ifNull(t1.brand,t2.brand) brand, " +

​ " ifNull(todayAmount,0.0) todaySalesAmount, " +

​ " ifNull(yestodayAmount,0.0) yestodaySalesAmount " +

​ "from " +

​ "(select brand,sum(saleAmount) todayAmount from di where toDate(dt) = today() group by brand) t1 " +

​ "full join " +

​ "(select brand,sum(saleAmount) yestodayAmount from di where toDate(dt) = yesterday() group by brand) t2 " +

​ "on t1.brand = t2.brand ")

List queryPhoneSales();

}

5.4.3 编写ServiceImpl

根据可视化图表中接口需要返回的数据格式，编写Service的方法实现。

@Service
public class DataInterfaceServiceImpl implements DataInterfaceService
{

@Autowired

private CKMapper ckMapper;

//案例三

@Override

public JSONObject querySalesAmountToday() {

​ List datas = ckMapper.queryPhoneSalesToday(LocalDate.now().toString());

​ List scores = new ArrayList<>();

​ List brands = new ArrayList<>();

​ List series = new ArrayList<>(1);

​ for (PhoneSales data : datas) {

​ scores.add(data.getSalesAmount());

​ brands.add(data.getBrand());

​ }

​ JSONObject result = getJsonObject(scores, brands, series);

​ return result;

}

//案例四

@Override

public JSONObject querySalesAmount() {

​ List data = ckMapper.queryPhoneSales();

​ List today = new ArrayList<>();

​ List yesterday = new ArrayList<>();

​ List brands = new ArrayList<>();

​ List series = new ArrayList<>(2);

​ for (PhoneSalesCompare d : data) {

​ today.add(d.getTodaySalesAmount());

​ yesterday.add(d.getYestodaySalesAmount());

​ brands.add(d.getBrand());

​ }

​ JSONObject todayJO = new JSONObject();

​ JSONObject yesterdayJO = new JSONObject();

​ JSONObject result = new JSONObject();

​ JSONObject dataJO = new JSONObject();

​ todayJO.put(“name”,“今天”);

​ todayJO.put(“data”,today);

​ yesterdayJO.put(“name”,“昨天”);

​ yesterdayJO.put(“data”,yesterday);

​ series.add(todayJO);

​ series.add(yesterdayJO);

​ dataJO.put(“categories”,brands);

​ dataJO.put(“series”,series);

​ result.put(“status”,0);

​ result.put(“msg”,“”);

​ result.put(“data”,dataJO);

​ return result;

}

}

5.4.4 编写Controller

@RestController
public class DataInterfaceController
{
@Autowired
private DataInterfaceService dIService;

//案例三

@RequestMapping(value = “/phoneSalesAmount”)

public Object queryPhoneSalesAmountToday(){

​ return dIService.querySalesAmountToday();

}

//案例四

@RequestMapping(value = “/phoneSalesAmountCompare”)

public Object queryPhoneSalesAmount(){

​ return dIService.querySalesAmount();

}
}

5.4.5 测试

案例三：

案例四：

5.5 从ES中返回数据

源数据结构:

{ “mappings”:

{ “_doc”:

{ “properties”:

{

“_class”:

{ “type”: “keyword”, “index”: false, “doc_values”: false },

“id”: { “type”: “long” },

“name”: { “type”: “keyword” },

“profession”: { “type”: “keyword” },

“sex”: { “type”: “keyword” },

“source”: { “type”: “keyword” }

}

}

}

}

案例五：查询ES中手机购买用户的访问渠道统计信息。效果图:

案例六：查询ES中手机购买用户的行业背景及性别比例。效果图:

ES是NoSql数据库，这里使用SpringData中提供的ElasticsearchRestTemplate客户端访问数据库。

5.5.1 封装Bean

封装Bean映射ES目标index中的数据模型。

@Data

@NoArgsConstructor

@AllArgsConstructor

@Document(indexName = “di”)

public class Customer

{

@Id

private Integer id;

@Field(type = FieldType.Keyword)

private String name;

@Field(type = FieldType.Keyword)

private String sex;

@Field(type = FieldType.Keyword)

private String source;

@Field(type = FieldType.Keyword)

private String profession;

}

5.5.2 编写Dao及Impl

public interface ESDao

{

SearchHits queryFromES(AbstractAggregationBuilder aggregationBuilder,Class t);

}

在ESDao中编写通用的聚合查询方法。并提供实现如下：

@Repository
public class ESDaoImpl implements ESDao
{
@Autowired
private ElasticsearchRestTemplate et;
@Override
public SearchHits queryFromES(AbstractAggregationBuilder aggregationBuilder, Class t) {
Query query = new NativeSearchQueryBuilder()
.addAggregation(aggregationBuilder)
.build();

​ SearchHits searchHits = et.search(query, t);
​ return searchHits;
}
}

5.5.3 编写ServiceImpl

根据可视化图表中接口需要返回的数据格式，编写Service的方法实现。

@Service
public class DataInterfaceServiceImpl implements DataInterfaceService
{

@Autowired

private ESDao esDao;

//案例五

@Override

public JSONObject querySourceData() {

​ TermsAggregationBuilder aggregation = AggregationBuilders.terms(“sc”)

​ .field(“source”);

​ SearchHits searchHits = esDao.queryFromES(aggregation, Customer.class);

​ Aggregations aggregations = searchHits.getAggregations();

​ Terms tagg= aggregations.get(“sc”);

​ List<? extends Terms.Bucket> elasticBucket = tagg.getBuckets();

​ List data = new ArrayList<>();

​ elasticBucket.forEach(el -> {

​ data.add(new SourceInfo(el.getKeyAsString(),el.getDocCount()));

​ });

​ JSONObject jsonObject = new JSONObject();

​ jsonObject.put(“status”,0);

​ jsonObject.put(“msg”,“”);

​ jsonObject.put(“data”,data);

​ return jsonObject;

}

//案例六

@Override

public JSONObject queryIndustryStats() {

​ TermsAggregationBuilder termsAggregationBuilder = AggregationBuilders

​ .terms(“pc”).field(“profession”).size(10)

​ .subAggregation(AggregationBuilders.terms(“sc”).field(“sex”).size(2));

​ SearchHits searchHits = esDao.queryFromES(termsAggregationBuilder, Customer.class);

​ Aggregations aggregations = searchHits.getAggregations();

​ List categeryList = new ArrayList<>();

​ List maleList = new ArrayList<>();

​ List femaleList = new ArrayList<>();

​ Terms byCompanyAggregation = aggregations.get(“pc”);

​ List<? extends Terms.Bucket> elasticBucket = byCompanyAggregation.getBuckets();

​ elasticBucket.forEach(el -> {

​ categeryList.add(el.getKeyAsString());

​ Terms sexAggregation = el.getAggregations().get(“sc”);

​ List<? extends Terms.Bucket> buckets = sexAggregation.getBuckets();

​ buckets.forEach(d -> {

​ if (“男”.equals(d.getKeyAsString())) {

​ double malePercent = d.getDocCount() * 100 / el.getDocCount() ;

​ maleList.add((int)malePercent);

​ femaleList.add(100 - (int)malePercent);

​ }

​ });

​ });

​ List series = Arrays.asList(

​ new IndustryInfo(“男”, “%”, maleList),

​ new IndustryInfo(“女”, “%”, femaleList)

​ );

​ JSONObject dataJO = new JSONObject();

​ dataJO.put(“categories”,categeryList);

​ dataJO.put(“series”,series);

​ dataJO.put(“yUnit”,“%”);

​ JSONObject resultJO = new JSONObject();

​ resultJO.put(“status”,0);

​ resultJO.put(“msg”,“”);

​ resultJO.put(“data”,dataJO);

​ return resultJO;

}

}

5.5.4 编写Controller

@RestController
public class DataInterfaceController
{
@Autowired
private DataInterfaceService dIService;

//案例五

@RequestMapping(value = “/sourceInfo”)

public Object querySourceInfo(){

​ return dIService.querySourceData();

}

//案例六

@RequestMapping(value = “/sexcompare”)

public Object querySexcompare(){

​ return dIService.queryIndustryStats();

}
}

5.5.5 测试

案例五：

案例六：

5.6 从HBase中返回数据

源数据结构:

案例七：查询各省份今日的订单数和销售额。效果图:

案例八：查询各省份今日的千万销售额目标完成进度。效果图:

HBase是一个NoSQL数据库，这里使用Phoenix编写SQL查询HBase。Phoenix支持JDBC操作，这里使用Mybatis简化JDBC操作。

5.6.1 封装Bean

@Data

@AllArgsConstructor

@NoArgsConstructor

public class ProvinceStat

{

private String name;

private Double value;

private Integer sizeValue;

private String url = “sugar.baidu.com”;

}

5.6.2 编写Mapper

@Mapper

@DS(“hbase”)

public interface HBaseMapper

{

//案例七

@Select("select province name , sum(ordercount) sizeValue , sum(saleamount) “value” " +

​ " from di " +

​ " where substr(to_char(saletime),1,10) = #{date} " +

​ " group by province")

List queryProvinceStatToday(String date);

//案例八

@Select("select sum(saleamount) " +

​ " from di " +

​ " where substr(to_char(saletime),1,10) = #{date}" +

​ " and province = #{name}")

Double queryCompletionByProvinceName(@Param(“name”) String name,@Param(“date”) String date);

}

5.6.3 编写ServiceImpl

根据可视化图表中接口需要返回的数据格式，编写Service的方法实现。

@Service
public class DataInterfaceServiceImpl implements DataInterfaceService
{

@Autowired

private HBaseMapper hBaseMapper;

//案例七

@Override

public JSONObject queryProvinceStatToday() {

​ List mapData = hBaseMapper.queryProvinceStatToday(LocalDate.now().toString());

​ JSONObject dataJO = new JSONObject();

​ dataJO.put(“mapData”,mapData);

​ dataJO.put(“valueName”,“销售额”);

​ dataJO.put(“sizeValueName”,“下单数”);

​ JSONObject result = new JSONObject();

​ result.put(“status”,0);

​ result.put(“msg”,“”);

​ result.put(“data”,dataJO);

​ return result;

}

//案例八

@Override

public JSONObject queryProviceCompletion(String city) {

​ Double amount = hBaseMapper.queryCompletionByProvinceName(city, LocalDate.now().toString());

​ double completion = 0;

​ if (amount==null){

​ completion = 0;

​ }else if (amount >= 1000000){

​ completion = 100;

​ }else{

​ completion = amount / 10000000 * 100;

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

e”,“下单数”);

​ JSONObject result = new JSONObject();

​ result.put(“status”,0);

​ result.put(“msg”,“”);

​ result.put(“data”,dataJO);

​ return result;

}

//案例八

@Override

public JSONObject queryProviceCompletion(String city) {

​ Double amount = hBaseMapper.queryCompletionByProvinceName(city, LocalDate.now().toString());

​ double completion = 0;

​ if (amount==null){

​ completion = 0;

​ }else if (amount >= 1000000){

​ completion = 100;

​ }else{

​ completion = amount / 10000000 * 100;

[外链图片转存中…(img-ziPJRd78-1714802038272)]
[外链图片转存中…(img-PEVkyvAq-1714802038272)]
[外链图片转存中…(img-ZLuwXi3p-1714802038273)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取