data-analysis-backup.sql

package mysql
-------医疗消费分析------------
-- 饼状图 默认值 1：商品销售 2：挂号 3：住院每日消费 4.住院门诊
-- 41：住院门诊化验  42：住院门诊影像 43：住院门诊处方 44：住院门诊手术
-- 45：住院门诊处置  5：住院商品销售 6：普通门诊 61：普通门诊化验
-- 62：普通门诊影像 63：普通门诊处方 64：普通门诊手术 65：普通门诊处置
-- 7：美容 8：寄养每日消费 9：寄养商品销售 10：疫苗 11:驱虫 12：订单结算商品销售',
SELECT
    consumption.top_category_id,
    con._name,
    convert(ifnull(sum(consumption.quantity*consumption.payprice), 0) / 1000000, decimal(11,3))
FROM
    his_consumption consumption
    left JOIN his_bill bill ON consumption.his_bill_id = bill.id
    LEFT JOIN con_category  con ON consumption.top_category_id =  con.id
WHERE
    consumption.delflag = 0
  AND consumption.sys_hospital_id IN ( 0, 1 )
  AND bill.billtype  IN (1,4,6,7)
  and consumption.his_retreatbill_id = 0
  and consumption.commodity_ismeal = 0
  and consumption.meter_num = 0
  AND consumption.top_category_id IN ( 1,2,3,4,5,6,7,8,9,10)
GROUP BY
    consumption.top_category_id
-- 下方数据
SELECT
    consumption.top_category_id as top_id ,
    con._name as _name,
    convert(ifnull(sum(consumption.quantity*consumption.payprice), 0) / 1000000, decimal(11,3)) AS money,
			DATE_FORMAT(consumption.createtime,'%Y-%m-%d') AS _time
FROM
    his_consumption consumption
    left JOIN his_bill bill ON consumption.his_bill_id = bill.id
    LEFT JOIN con_category  con ON consumption.top_category_id =  con.id
WHERE
    consumption.delflag = 0
  AND consumption.sys_hospital_id IN ( 0, 1 )
  AND bill.billtype  IN (1,4,6,7)
  and consumption.his_retreatbill_id = 0
  and consumption.commodity_ismeal = 0
  and consumption.meter_num = 0
  AND consumption.top_category_id IN ( 1,2,3,4,5,6,7,9,10)
  AND 	DATE_FORMAT(consumption.createtime,'%Y-%m-%d') BETWEEN "2021-01-01" AND "2022-01-01"
GROUP BY
    consumption.top_category_id,DATE_FORMAT(consumption.createtime,'%Y-%m-%d')
---- 商品分析
---商品数量以及分类数量
-- 商品库存总量
select
    COUNT(quantity)
from
    g_stock
where
        delflag = 0
  and sys_hospital_id = 1
-- 服务商品总量
--商品总种类
select
    count( 0 ) as counts
FROM
    (
        SELECT
            commodity.id
        FROM
            g_stock stock
                LEFT JOIN con_commodity commodity ON stock.con_commodity_id = commodity.id
                LEFT JOIN con_category category ON category.id = commodity.con_category_id
        WHERE
                stock.delflag = 0
          AND stock.sys_hospital_id = 1
          AND commodity.top_category_id IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,11,12)
        GROUP BY
            commodity.id
    ) a
-- 普通商品
select
    count( 0 ) as counts
FROM
    (
        SELECT
            commodity.id
        FROM
            g_stock stock
                LEFT JOIN con_commodity commodity ON stock.con_commodity_id = commodity.id
                LEFT JOIN con_category category ON category.id = commodity.con_category_id
        WHERE
                stock.delflag = 0
          AND stock.sys_hospital_id = 1
          AND commodity.top_category_id IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
        GROUP BY
            commodity.id
    ) a
-- 商品其他种类 在in中填写 种类id
select
    count( 0 ) as counts
FROM
    (
        SELECT
            commodity.id
        FROM
            g_stock stock
                LEFT JOIN con_commodity commodity ON stock.con_commodity_id = commodity.id
                LEFT JOIN con_category category ON category.id = commodity.con_category_id
        WHERE
                stock.delflag = 0
          AND stock.sys_hospital_id = 1
          AND commodity.top_category_id IN ( 11)
        GROUP BY
            commodity.id
    ) a
--
select
    count( 0 ) as counts
FROM
    (
        SELECT
            commodity.id
        FROM
            g_stock stock
                LEFT JOIN con_commodity commodity ON stock.con_commodity_id = commodity.id
                LEFT JOIN con_category category ON category.id = commodity.con_category_id
        WHERE
                stock.delflag = 0
          AND stock.sys_hospital_id = 1
          AND commodity.top_category_id IN ( 12)
        GROUP BY
            commodity.id
    ) a
--商品警惕
SELECT
    c.con_commodity_id,
    c.commodity_name,
    COUNT(0)
FROM
    his_consumption AS c
        LEFT JOIN his_bill AS b ON b.id = c.his_bill_id
        LEFT JOIN g_stock AS g ON g.con_commodity_id = c.con_commodity_id
WHERE
        c.delflag = 0
  AND c.sys_hospital_id = 1
  AND c.his_retreatbill_id = 0
  AND c.commodity_ismeal = 0
  AND c.meter_num = 0
  AND b.billtype IN ( 1, 4, 6, 7 )
  and DATE_FORMAT(b.paytime, "%Y-%m-%d") BETWEEN "2021-01-01"  AND "2022-01-01"
GROUP BY
    c.con_commodity_id

--商品销售分析
SELECT
    c.con_commodity_id,
    c.commodity_name,
    c.top_category_id,
    SUM(c.quantity) ,
    CONVERT (
                ifnull( sum( c.quantity * c.payprice ), 0 ) / 1000000,
                DECIMAL ( 11, 3 )),
    CONVERT (
                ifnull( sum( c.quantity * g.costmoney ), 0 ) / 1000000,
                DECIMAL ( 11, 3 ))
FROM
    his_consumption AS c
        LEFT JOIN his_bill AS b ON b.id = c.his_bill_id
        LEFT JOIN g_stock AS g ON g.con_commodity_id = c.con_commodity_id
WHERE
        c.delflag = 0
  AND c.sys_hospital_id = 1
  AND c.his_retreatbill_id = 0
  AND c.commodity_ismeal = 0
  AND c.meter_num = 0
  AND b.billtype IN ( 1, 4, 6, 7 )
GROUP BY
    c.con_commodity_id
---员工分析
-- 首先查出所有的已结订单
--B端小程序
--- 数据概览
-- 新增用户
select count(*) from his_consumer where delflag = 0 and DATE_FORMAT(addtime, ?) = ?
--新增会员(新增会员卡与次卡去重后就是新增会员)
--  新增的会员卡用户
SELECT
    c.id
FROM
    his_consumer c
        INNER JOIN his_card card ON c.id = card.his_consumer_id
        AND c.id not IN (select his_consumer_id  FROM  his_card WHERE delflag = 0 AND sys_hospital_id = 1 AND  createtime < "2021-01-01")
        AND c.id not IN (select his_consumer_id  FROM  his_metercard WHERE delflag = 0 AND sys_hospital_id = 1 AND  createtime < "2021-01-01")
-- 新增的次卡用户
SELECT
    c.id
FROM
    his_consumer c
        INNER JOIN his_metercard card ON c.id = card.his_consumer_id
        AND c.id not IN (select his_consumer_id  FROM  his_card WHERE delflag = 0 AND sys_hospital_id = 1 AND  createtime < "2021-01-01")
        AND c.id not IN (select his_consumer_id  FROM  his_metercard WHERE delflag = 0 AND sys_hospital_id = 1 AND  createtime < "2021-01-01")
--- 新客消费 老客消费  到店消费
--  新客
SELECT
    his_consumer_id,
    SUM( paymoney / 1000 ) price
FROM
    his_bill
WHERE
        delflag = 0
  AND his_consumer_id > 0
  AND billtype != 5
	AND billtype != 7
	AND sys_hospital_id = 1
	AND DATE_FORMAT( paytime, '%Y-%m-%d' ) = "2021-04-06"
	AND his_consumer_id NOT IN (
	SELECT
		his_consumer_id
	FROM
		his_bill
	WHERE
		delflag = 0
		AND his_consumer_id > 0
		AND billtype != 5
		AND billtype != 7
		AND sys_hospital_id = 1
		AND DATE_FORMAT( paytime, '%Y-%m-%d' ) < "2021-04-06"
	GROUP BY
		his_consumer_id
	)
GROUP BY
    DATE_FORMAT( paytime, '%Y-%m-%d' ),
    his_consumer_id
-- 老客
SELECT
    his_consumer_id,
    SUM( paymoney / 1000 ) price
FROM
    his_bill
WHERE
        delflag = 0
  AND his_consumer_id > 0
  AND billtype != 5
	AND billtype != 7
	AND sys_hospital_id = 1
	AND date_format( paytime, '%Y-%m-%d' ) = "2021-04-06"
	AND his_consumer_id IN (
	SELECT
		his_consumer_id
	FROM
		his_bill
	WHERE
		delflag = 0
		AND his_consumer_id > 0
		AND billtype != 5
		AND billtype != 7
		AND sys_hospital_id = 1
		AND DATE_FORMAT( paytime, '%Y-%m-%d' ) < "2021-04-06"
	GROUP BY
		his_consumer_id
	)
GROUP BY
    DATE_FORMAT( paytime, '%Y-%m-%d' ),
    his_consumer_id
-- 到店 为 新客加老客

-- 次卡分析
-- 次卡分析按时间与次卡id
-- 次卡充值的可以使用的次数SUM( meter ),与新开次卡张数 COUNT( 0 )
SELECT
    DATE_FORMAT( card.createtime, '%Y-%m-%d' ) ,
    card.con_metercardtype_id,
    type._name,
    ifnull( SUM( meter ), 0 ),
    COUNT( 0 )
FROM
    dbc_hueqzfp2kn.his_metercard card
    LEFT JOIN dbc_hueqzfp2kn.con_metercardtype type ON type.id = card.con_metercardtype_id
WHERE
    card.delflag = 0
  AND card.sys_hospital_id = 1
  AND DATE_FORMAT( card.createtime, '%Y-%m-%d' ) BETWEEN "2021-01-01"
  AND  "2022-01-01"
  AND 	card.con_metercardtype_id = 3
GROUP BY
    DATE_FORMAT( card.createtime, '%Y-%m-%d' )
-- 次卡分析之详细数据
SELECT
    DATE_FORMAT( meter_detail.eventtime, '%Y-%m-%d' ),
    SUM( CONVERT ( ifnull( meter_detail.money, 0 ), DECIMAL ( 11, 3 )) ) / 1000 AS money,
    meter_type.id
FROM
    dbc_hueqzfp2kn.his_metercard_detail AS meter_detail
        LEFT JOIN dbc_hueqzfp2kn.his_metercard AS meter ON meter_detail.his_metercard_id = meter.id
        LEFT JOIN dbc_hueqzfp2kn.con_metercardtype AS meter_type ON meter.con_metercardtype_id = meter_type.id
WHERE
        meter_detail.sys_hospital_id = 1
  AND meter_detail.eventtype = 2
  AND meter_detail.delflag = 0
  AND DATE_FORMAT( meter_detail.eventtime, '%Y-%m-%d' ) BETWEEN "2021-01-01"
    AND "2022-01-01"
  AND meter_type.id = 2
GROUP BY
    DATE_FORMAT( meter_detail.eventtime, '%Y-%m-%d' )
-- 数据概览
-- 开卡 充值 排行
-- 会员卡
SELECT
    card_type._name,
    ifnull(SUM(card_pay.money) / 1000,0)
FROM
    his_card card
    LEFT JOIN  con_cardtype card_type ON card_type.id  = card.con_cardtype_id
    LEFT JOIN his_card_detail card_detail ON. card_detail.his_card_id = card.id
    left JOIN his_card_detail_pay  card_pay ON card_pay.his_card_detail_id = card_detail.id
    AND card.sys_hospital_id = 1
GROUP BY card.con_cardtype_id

-- 次卡
SELECT
    card_type._name,
    ifnull(SUM(card_pay.money),0)  / 1000
FROM
    his_metercard card
    LEFT JOIN  con_metercardtype card_type ON card_type.id  = card.con_metercardtype_id
    LEFT JOIN his_metercard_detail card_detail ON. card_detail.his_metercard_id = card.id
    left JOIN his_metercard_detail_pay  card_pay ON card_pay.his_metercard_detail_id = card_detail.id
    AND card.sys_hospital_id = 1
GROUP BY card.con_metercardtype_id


-- 押金
SELECT
        SUM(p.money) / 1000
FROM
    his_deposit_detail d
        LEFT JOIN his_deposit_detail_pay p ON d.id = p.his_deposit_detail_id
WHERE
        d.sys_hospital_id = 1

-- 新客户初诊
SELECT
    COUNT(0)
FROM
    (SELECT
         id,
         DATE_FORMAT( eventtime, '%Y-%m-%d' ) AS _time
FROM
         his_clinic
         WHERE
	delflag = 0
	AND sys_hospital_id = 1
	AND DATE_FORMAT( eventtime, '%Y-%m-%d' ) BETWEEN "2021-09-01"
	AND "2021-10-10"
	AND group_id = 0
	AND his_consumer_id NOT IN (
	SELECT
		his_consumer_id
	FROM
		his_clinic
	WHERE
		delflag = 0
		AND sys_hospital_id = 1
		AND DATE_FORMAT( eventtime, '%Y-%m-%d' ) < "2021-09-10"
	)
     GROUP BY his_consumer_id) as a