如何在不使用单个SQL语句的情况下在MySQL中组合多个计算?

问题描述

大家好，
我是MySQL的新手，所以我需要一些帮助.我正在写我的硕士论文，我需要使用SQL语句才能将来自MySQL的数据获取到可视化软件(该软件称为tableau)中，但是我完全被淹没了:(非常感谢您的帮助！最好，德西雷(Désirée)
这是我的问题:如何在一个SQL语句中获得以下4个计算:
•数据库:称为foto
•表rate_changes:包含以下变量:
o Is_trial
o Is_conversion
o Is_churn
o Is_upgrade
o Is_downgrade
o ID
o Photographer_id
o资格
o Marketing_channel_id
o小子
o Affiliate_id
o Old_rate_id
o New_rate_id
o已创建
为了计算#个订阅者，我需要进行以下计算:
(因为我不知道正确的SQL代码，所以我用一种元语言将其写下来，这只是SQL和我要执行的操作的描述的混合体)
计算1:

Hi guys,
I´m new to MySQL , so I would need some help. I´m writing my master thesis where I need to use SQL statements in order to get data from MySQL into a visualization software (software is called tableau) but I´m totally swamped :( would really appreciate your help!! Thanks a lot in advance! Best, Désirée
Here is my question: how do I get the following 4 calculations in one SQL statement:
•Database: is called foto
•The table rate_changes: contains following variables:
oIs_trial
oIs_conversion
oIs_churn
oIs_upgrade
oIs_downgrade
oId
oPhotographer_id
oQualification
oMarketing_channel_id
oKid
oAffiliate_id
oOld_rate_id
oNew_rate_id
oCreated
In order to calculate the # subscribers I need to do the following calculations:
(I write it down in a kind of meta language which is just a mix of SQL and the description of what I want to do, because I don´t know the correct SQL code)
Calculation 1:

Select count (foto.rate_changes.photographer_id)
From foto.rate_changes
Where foto.rate_changes.is_conversion = 1;

计算2:

Calculation 2:

Select count (foto.rate_changes.photographer_id)
From foto.rate_changes
Where foto.rate_changes.is_churn= 1;

计算3:result (calculation 1) – result (calculation 2)
计算4:running sum of calculation 3->然后您每月有#个订阅者

Calculation 3: result (calculation 1) – result (calculation 2)
Calculation 4: running sum of calculation 3 -->then you have the # subscribers per month

推荐答案

SELECT FT.Calculation1, FT.Calculation2, FT.Calculation1 - FT.Calculation2 AS Calculation3
FROM ((
   SELECT COUNT(foto.rate_changes.photographer_id) AS Calculation1
   FROM foto.rate_changes 
   WHERE foto.rate_changes.is_conversion = 1) AS t1
   INNER JOIN (
       SELECT COUNT(foto.rate_changes.photographer_id) AS Calculation2
       FROM foto.rate_changes
       WHERE foto.rate_changes.is_churn= 1) AS t2
   ON t1.Id = t2.Id) AS FT

这篇关于如何在不使用单个SQL语句的情况下在MySQL中组合多个计算?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！