1. Johngo学长首页
  2. 精选

数据分析：SQL和Python

• 精选 • 阅读 15

SQL

统计数据概况：计算样本总数、商家总数、用户总数、消费总数、领券总数等

select
    count(User_id) as '样本总数',
    count(distinct Merchant_id) as '商家总数',
    count(distinct User_id) as '用户总数',
    count(Date) as '消费总数',
    count(Date_received) as '领券总数',
    (select count(*) from ddm.offline_train as a where a.Date_received is not null and a.Date is not null) as '领券消费总数',
    (select count(*) from ddm.offline_train as a where a.Date_received is null and a.Date is not null) as '无券消费总数',
    (select count(*) from ddm.offline_train as a where a.Date_received is not null and a.Date is not null)/count(Date_received) as '核销率'
from ddm.offline_train

数据分析：SQL和Python

统计不同距离下：领券人数、用券消费人数、核销率

# 查找各距离的领券人数/用券消费人数/核销率
select
    Distance,
    count(Coupon_id) as get_coupons_num,
    sum(if(Date_received is not null and Date is not null,1,0)) as user_coupons_num,
    sum(if(Date_received is not null and Date is not null,1,0)) /count(Coupon_id) as use_coupons_rate
from ddm.offline_train
where Distance is not null
group by Distance
order by distance

数据分析：SQL和Python 消费券使用情况占比

# 消费券使用情况占比
with temp as (
    select
        case
            when Date_received is not null and Date is not null then '有券消费'
            when Date_received is not null and Date is null then '有券未消费'
            when Date_received is null and Date is not null then '无券消费'
        end as flag
    from ddm.offline_train
)
select
    flag as '优惠券使用情况',
    concat(round(count(flag)/(select count(*) from temp)*100,2),'%') as '百分比'
from temp
group by flag
order by count(flag)/(select count(*) from temp)

数据分析：SQL和Python

with as 也叫做子查询部分，类似于一个视图或临时表，可以用来存储一部分的sql语句查询结果，必须和其他的查询语句一起使用，且中间不能有分号，目前在oracle、sql server、hive等均支持 with as 用法，但 mysql并不支持！

不同类型优惠券的核销情况和平均领取距离

# 不同优惠券类型的核销情况和平均领取距离
select
    Discount_rate as '折扣',
    avg(Distance) as '平均距离',
    count(Date_received) as '领券人数',
    sum(if(Date_received is not null and Date is not null,1,0)) as '有券消费人数',
    sum(if(Date_received is not null and Date is not null,1,0))/count(Date_received) as '核销率'
from ddm.offline_train
where Date_received is not null
group by Discount_rate
order by '有券消费人数' desc

数据分析：SQL和Python

不同满减门槛的核销情况

# 不同满减门槛的核销情况
select
    mk as '门槛',
    count(*) as '领券数量',
    sum(if(Date is not null,1,0)) as '用券消费数量',
    concat(round(sum(if(Date is not null,1,0))/count(*)*100,2),'%') as '核销率'
from(select
         DATE,
         convert(if(Discount_rate like '%.%',0,Discount_rate),signed) as mk
    from ddm.offline_train) as aa
where mk is not null
group by mk
order by mk

数据分析：SQL和Python

不同核销率的商家分布情况（占比）

# 不同核销率用户分布
with temp as (
    select
        Merchant_id,
        count(Date_received) as get_num,
        sum(if(Date is not null and Date_received is not null,1,0)) as use_num,
        sum(if(Date is not null and Date_received is not null,1,0))/count(Date_received) as Merchant_rate
    from ddm.offline_train
    where Date_received is not null
    group by Merchant_id
)

select
    tag,
    concat(round(count(*)/(select count(*) from temp)*100,2),'%') as Merchant_percent
from(
    select
        Merchant_id,
        case
            when Merchant_rate = 0 then '核销率：0'
            when Merchant_rate > 0 and Merchant_rate < 0.2 then '核销率：0-20%'
            when Merchant_rate >= 0.2 and Merchant_rate< 0.3 then '核销率：20%-30%'
            when Merchant_rate >= 0.3 and Merchant_rate< 0.5 then '核销率：30%-50%'
            when Merchant_rate >= 0.5 then '核销率：50%以上'
        end as tag
    from temp
    )aa
group by tag
order by Merchant_percent desc

数据分析：SQL和Python 不同领券次数商家的分布情况（平均核销率/占比） 
# 不同领券次数用户分布-平均核销率/占比
with temp as (
    select
        Merchant_id,
        count(Date_received) as get_num,
        sum(if(Date is not null and Date_received is not null,1,0))/count(Date_received) as user_rate,
        sum(if(Date is not null and Date_received is not null,1,0)) as use_num,
        case
            when count(Date_received)>100 then '100次以上'
            when count(Date_received)=0 then '0次'
            when count(Date_received) between 1 and 10 then '1-10次'
            when count(Date_received) between 11 and 50 then '11-50次'
            when count(Date_received) between 51 and 100 then '51-100次'
            else '其他次'
        end as flag
    from ddm.offline_train
    group by Merchant_id
)

select
    flag as '被领券次数',
    concat(round(avg(user_rate)*100,2),'%') as Merchant_avg_use_rate,
    concat(round(count(*)/(select count(*) from temp)*100,2),'%') as Merchant_percent
from temp
group by flag
order by (count(*)/(select count(*) from temp)) desc

数据分析：SQL和Python

不同核销率用户分布（占比）

# 不同核销率用户分布
with temp as (
    select
        User_id,
        count(Date_received) as get_num,
        sum(if(Date is not null and Date_received is not null,1,0)) as use_num,
        sum(if(Date is not null and Date_received is not null,1,0))/count(Date_received) as user_rate
    from ddm.offline_train
    where Date_received is not null
    group by User_id
)

select
    tag,
    concat(round(count(*)/(select count(*) from temp)*100,2),'%') as user_percent
from(
    select
        User_id,
        case
            when user_rate = 0 then '核销率：0'
            when user_rate > 0 and user_rate < 0.3 then '核销率：0-30%'
            when user_rate >= 0.3 and user_rate< 0.5 then '核销率：30%-50%'
            when user_rate >= 0.5 then '核销率：50%以上'
        end as tag
    from temp
    )aa
group by tag
order by user_percent desc

数据分析：SQL和Python

不同月份优惠券领券次数/核销次数/核销率

# 不同月份领券次数/核销次数/核销率
select
    month,
    coupons_get_num,
    coupons_use_num,
    concat(round(coupons_use_num/coupons_get_num*100,2),'%') as coupons_use_rate
from(select
        month(Date_received) as month,
        count(*) as coupons_get_num
    from ddm.offline_train
    where Date_received is not null
    group by month(Date_received)) as a
inner join(
    select
        month(Date) as month,
        count(*) as coupons_use_num
    from ddm.offline_train
    where Date_received is not null and Date is not null
    group by month(Date)
)as b using(month)
order by month

数据分析：SQL和Python

不同工作日的优惠券平均核销周期、核销率

# 工作日平均核销间隔、核销率
with get_coupons as(
    select
        weekday(Date_received)+1 as coupons_day,
        count(*) as coupons_get_num
    from ddm.offline_train
    where Date_received is not null
    group by weekday(Date_received)+1
),
use_coupons as(
    select
        weekday(Date)+1 as coupons_day,
        count(*) as coupons_use_num,
        round(avg(datediff(Date,Date_received)),2) as use_interval
    from ddm.offline_train
    where Date is not null and Date_received is not null
    group by weekday(Date)+1
)

select
    coupons_day,
    use_interval,
    concat(round(coupons_use_num/coupons_get_num*100,2),'%') as coupons_use_rate
from get_coupons
inner join use_coupons using(coupons_day)
order by coupons_day

数据分析：SQL和Python

数据分析：SQL和Python

Python

库包

  1. pandas: 支持从CSV、JSON、SQL、Microsoft Excel load数据，可以对各种维度的数据做归并、再成形、选择，还有数据清洗和数据加工特征
  2. numpy：存储和操作矩阵、数组计算的包：数组计算、逻辑运算、傅立叶变化和图形操作、跟线性代数相关的操作
  3. Matplotlib：绘图工具可以绘制线图、散点图、等高线图、条形图、柱状图、3D 图形、甚至是图形动画等。
  4. sklearn：它涵盖了分类、回归、聚类、降维、模型选择、数据预处理六大模块，降低机器学习实践门槛，将复杂的数学计算集成为简单的函数，并提供了众多公开数据集和学习案例。

数据预处理

data = pd.read_csv(r"/Users/xll/Documents/服务数据质量/测试数据1.csv")

#返回数据集行和列的元组，其中data.shape[0]代表返回行数，data.shape[1] 代表返回列数
print(data.shape)

#返回数据集的所有列名
data.columns

#随机返回样本5行
data.sample(5)

#返回前5行
print(data.head(5))

#返回浮点型和正行字段的均值、最大值、等统计数据
print(data.describe())

#numpy包的这个方法也是可以得到同样的结果
import numpy as np
print(data.describe(include=[np.number]))

数据分析：SQL和Python

数据分析：SQL和Python

数据分析：SQL和Python

参考：

https://blog.csdn.net/twlve/article/details/128609147?spm=1001.2014.3001.5502

https://blog.csdn.net/twlve/article/details/128626526?spm=1001.2014.3001.5502

O2O优惠券核销-数据分析_十二十二呀的博客-CSDN博客_优惠券数据分析

Original: https://blog.csdn.net/Strive_0902/article/details/128791541
Author: Strive_0902
Title: 数据分析：SQL和Python

相关阅读

Title: STM32 例程-7 STM32固件库方式 读取SHT20 温湿度

SHT20 .h文件

#ifndef SHT20_H
#define SHT20_H
#include "common.h"
#include "stdio.h"
#include "string.h"
#include "stm32f4xx.h"

#define IIC_SCL_PIN  GPIO_Pin_0
#define IIC_SDA_PIN  GPIO_Pin_1
#define SHT20_ADDRESS  0x80
#define IIC_SCL_Set()  GPIO_WriteBit(GPIOB, IIC_SCL_PIN, Bit_SET)
#define IIC_SCL_Clr()  GPIO_WriteBit(GPIOB, IIC_SCL_PIN, Bit_RESET)

#define IIC_SDA_Set()  GPIO_WriteBit(GPIOB, IIC_SDA_PIN, Bit_SET)
#define IIC_SDA_Clr()  GPIO_WriteBit(GPIOB, IIC_SDA_PIN, Bit_RESET)

#define IIC_SDA_Get()  GPIO_ReadInputDataBit(GPIOB, IIC_SDA_PIN)
#define IIC_SCL_Get()  GPIO_ReadInputDataBit(GPIOB, IIC_SCL_PIN)
#ifndef IIC_Direction_Transmitter
    #define  IIC_Direction_Transmitter      ((uint8_t)0x00)
#endif

#ifndef IIC_Direction_Receiver
    #define  IIC_Direction_Receiver         ((uint8_t)0x01)
#endif

#define  IIC_SDA_IN   0
#define  IIC_SDA_OUT  1

enum
{
    IIC_ACK,
    IIC_NACK
};

//IIC&#x6240;&#x6709;&#x64CD;&#x4F5C;&#x51FD;&#x6570;
void IIC_GPIOInit(void);
void IIC_SDAMode(uint8_t Mode);
void IIC_Start(void);
void IIC_Stop(void);
uint8_t IIC_WaitForAck(void);
void IIC_Ack(void);
void IIC_NAck(void);
void IIC_SendByte(uint8_t Data);
uint8_t IIC_ReadByte(unsigned char Ack);
void SHT_GPIO_Init(void);
void SHT20_Init(void);
void SHT20_read_result(void);
#endif

SHT20 .c文件

#include "SHT20.h"
#include "common.h"
#include "lcd.h"
//IO&#x65B9;&#x5411;&#x8BBE;&#x7F6E;
#define SDA_IN()  {GPIOB->MODER&=~(3<<(1*2));gpiob->MODER|=0<<1*2;} pb1输入模式 #define sda_out() {gpiob->MODER&=~(3<<(1*2));gpiob->MODER|=1<<1*2;} pb1输出模式 void sht_gpio_init(void) { gpio_inittypedef gpio_initstructure; rcc_ahb1periphclockcmd( rcc_ahb1periph_gpiob, enable ); 使能gpiob gpio_initstructure.gpio_pin="IIC_SCL_PIN|IIC_SDA_PIN;//PB0,PB1" gpio_initstructure.gpio_mode="GPIO_Mode_OUT;" 开漏输出 gpio_initstructure.gpio_otype="GPIO_OType_PP;" gpio_initstructure.gpio_speed="GPIO_Speed_100MHz;" gpio_init(gpiob, &gpio_initstructure); gpio_initstructure.gpio_pupd="GPIO_PuPd_UP;//&#x4E0A;&#x62C9;" iic_scl_set(); iic_sda_set(); } iic_delay(uint16_t time) uint8_t i; while(time --) for(i="0;" i < 10; ++); iic_sdamode(uint8_t mode){ if(mode){ sda_out(); }else{ sda_in(); 产生iic起始信号 iic_start(void) iic_sdamode(iic_sda_out); iic_delay(4); iic_sda_clr(); iic_scl_clr(); 产生iic停止信号 iic_stop(void) 等待应答信号到来 返回值：1，接收应答失败 0，接收应答成功 iic_waitforack(void) retry="0;" iic_delay(1); iic_sdamode(iic_sda_in); iic_sda_set();iic_delay(1); iic_scl_set();iic_delay(1); while(iic_sda_get()) if(++> 250)
        {
            IIC_Stop();
            return 1;
        }
    }
    IIC_SCL_Clr();

    return 0;
}
//&#x4EA7;&#x751F;ACK&#x5E94;&#x7B54;
void IIC_Ack(void)
{
    IIC_SCL_Clr();
    IIC_SDAMode(IIC_SDA_OUT);
    IIC_SDA_Clr();
    iic_delay(2);
    IIC_SCL_Set();
    iic_delay(2);
    IIC_SCL_Clr();
}
//&#x4EA7;&#x751F;&#x975E;ACK&#x5E94;&#x7B54;
void IIC_NAck(void)
{
    IIC_SCL_Clr();
    IIC_SDAMode(IIC_SDA_OUT);
    IIC_SDA_Set();
    iic_delay(2);
    IIC_SCL_Set();
    iic_delay(2);
    IIC_SCL_Clr();
}
//IIC&#x53D1;&#x9001;&#x4E00;&#x4E2A;&#x5B57;&#x8282;
//&#x8FD4;&#x56DE;&#x4ECE;&#x673A;&#x6709;&#x65E0;&#x5E94;&#x7B54;
//1&#xFF0C;&#x6709;&#x5E94;&#x7B54;
//0&#xFF0C;&#x65E0;&#x5E94;&#x7B54;
void IIC_SendByte(uint8_t Data)
{
  uint8_t i;
    IIC_SDAMode(IIC_SDA_OUT);
    IIC_SCL_Clr();
    for(i = 0; i < 8; i ++)
    {
        if(Data & 0x80)
            IIC_SDA_Set();
        else
            IIC_SDA_Clr();
        Data <<= 175 1; iic_delay(1); iic_scl_set(); iic_delay(2); iic_scl_clr(); } 读1个字节，ack="1&#x65F6;&#xFF0C;&#x53D1;&#x9001;ACK&#xFF0C;ack=0&#xFF0C;&#x53D1;&#x9001;nACK" uint8_t iic_readbyte(unsigned char ack) { i, recdat="0;" iic_sdamode(iic_sda_in); for(i="0;" i < 8; ++) iic_delay(55); <<="1;" if(iic_sda_get()) |="0x01;" else &="~0x01;" iic_delay(5); if(ack) iic_ack(); iic_nack(); return recdat; void sht20_init(void) sht_gpio_init(); iic_delay(50); 0x2130 表示周期模式 周期1s iic_start(); iic_sendbyte(0x88); iic_waitforack(); iic_sendbyte(0x21); iic_sendbyte(0x30); iic_stop(); iic_delay(5000); ******************************************************************* 温湿度获取函数 函数原型: sht20_read_result(uint8_t addr); 功能: 用来接收从器件采集并合成温湿度 ******************************************************************** sht20_read_result(void) uint16_t temp_humi_buffer[6],temp,humi; float temperature,humidity; 写命令给sht20，这个命令是访问sht20转换结果的寄存器的 iic_sendbyte(0x80); iic_sendbyte(0xf3); delay_ms(100); iic_sendbyte(0x81); temp_humi_buffer[0]="IIC_ReadByte(1);" temp_humi_buffer[1]="IIC_ReadByte(1);" temp_humi_buffer[2]="IIC_ReadByte(1);" temp="((temp_humi_buffer[0]<<8)" temp_humi_buffer[1]); 温度拼接 iic_sendbyte(0xf5); humi="((temp_humi_buffer[0]<<8)" 湿度拼接 *转换实际温度* temperature="(175.0f*(float)temp/65535.0f" -45.0f); t="-45" + * tem (2^16-1) humidity="(100.0f*(float)humi/65535.0f);//" rh="hum*100" if((temperature>=-40)&&(temperature<=125)&&(humidity>=0)&&(humidity<=100)) 过滤错误 { u8 str[256]; memset(str,0x00,256); sprintf((char*)str,"temp&hum:%6.2f*c %6.2f%%",temperature,humidity); lcd_displaystring(10,250,16,str); printf("温湿度：%6.2f*c }else{ printf("温湿度故障"); } delay_ms(1000); < code></=100))></=125)&&(humidity></=></1*2;}></(1*2));gpiob-></1*2;}></(1*2));gpiob->

调用方法
SHT20_Init(); //SHT20初始化
SHT20_read_result();

本博客是个人工作中记录，遇到问题可以互相探讨，没有遇到的问题可能没有时间去特意研究，勿扰。

[En]

This blog is a record of personal work, problems can be discussed with each other, do not encounter problems may not have time to study, do not disturb.

另外建了几个QQ技术群：
2、全栈技术群：616945527,加群口令abc123
2、硬件嵌入式开发: 75764412
3、Go语言交流群：9924600

闲置域名www.nsxz.com出售（等宽等高字符四字域名）。

Original: https://www.cnblogs.com/zhaogaojian/p/16095748.html
Author: zhaogaojian
Title: STM32 例程-7 STM32固件库方式 读取SHT20 温湿度

原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/363473/

转载文章受原作者版权保护。转载请注明原作者出处！

赞 (0)
0

【自取】最近整理的，有需要可以领取学习：



Linux核心资料大放送~

全栈面试题汇总（持续更新&可下载）

一个提高学习100%效率的工具！

【超详细】深度学习面试题目！

LeetCode Python刷题答案下载！

LeetCode Java版刷题答案下载！

LeetCode C++ 版本，抓紧保存！

LeetCode GO语言 刷题答案下载！

大家都在看

最近整理资源【免费获取】：   👉 程序员最新必读书单  | 👏 互联网各方向面试题下载 | ✌️计算机核心资源汇总