SQL按不同的条件求和2个不同的列，然后减法和加法 [英] SQL sum 2 different column by different condtion then subtraction and add

问题描述

我正在尝试的东西有点复杂，我会尽力解释.

what I am trying is kind of complex, I will try my best to explain.

我完成了第一部分，即按小时对列求和.

I achieved the first part which is to sum the column by hours.

示例

ID         TIMESTAMP          CUSTAFFECTED
1     10-01-2013 01:00:23       23
2     10-01-2013 03:00:23       55
3     10-01-2013 05:00:23       2369
4     10-01-2013 04:00:23       12
5     10-01-2013 01:00:23       1
6     10-01-2013 12:00:23       99
7     10-01-2013 01:00:23       22
8     10-01-2013 02:00:23       3

输出是

      Hour          TotalCALLS   CUSTAFFECTED
  10/1/2013 01:00       3         46        
  10/1/2013 02:00       1         3
  10/1/2013 03:00       1         55
  10/1/2013 04:00       1         12
  10/1/2013 05:00       1         2369
  10/1/2013 12:00       1         99

查询

    SELECT   TRUNC(STARTDATETIME, 'HH24') AS hour, 
           COUNT(*) AS TotalCalls,
           sum(CUSTAFFECTED) AS CUSTAFFECTED
    FROM     some_table
    where STARTDATETIME >= To_Date('09-12-2013 00:00:00','MM-DD-YYYY HH24:MI:SS') and
          STARTDATETIME <= To_Date('09-13-2013 00:00:00','MM-DD-YYYY HH24:MI:SS') and
GROUP BY TRUNC(STARTDATETIME, 'HH')

我需要什么我需要总结 2 个查询并按时间戳/小时分组.第二个查询与第一个完全相同，只是 where 子句不同.

what I need what I need sum 2 queries and group by timestamp/hour. 2nd query is exactly same as first but just the where clause is different.

第二次查询

SELECT   TRUNC(RESTOREDDATETIME , 'HH24') AS hour, 
     COUNT(*) AS TotalCalls, 
     SUM(CUSTAFFECTED) AS CUSTRESTORED
FROM     some_table
where RESTOREDDATETIME >= To_Date('09-12-2013 00:00:00','MM-DD-YYYY HH24:MI:SS') and
      RESTOREDDATETIME <= To_Date('09-13-2013 00:00:00','MM-DD-YYYY HH24:MI:SS') 
GROUP BY TRUNC(RESTOREDDATETIME , 'HH24')

所以我需要减去 custaffected - custrestoed，并显示总数.

so I need to subtract custaffected - custrestoed, and display tht total.

我添加了 Excel 文件的链接.http://goo.gl/ioo9hg

I added link to excel file. http://goo.gl/ioo9hg

谢谢

推荐答案

我最近需要这样做，并且不得不玩弄它以使其正常工作.

I recently needed to do this and had to play with it some to get it to work.

挑战在于获取一个查询的结果以链接到同一查询中的另一个查询，然后操作字段的返回值，以便将一个查询结果集中给定字段中的值称为 FieldA，从不同结果集中的字段中的值中减去，称为 FieldB.主题值是否是 COUNT(...) 等聚合函数的结果并不重要；它们可以是需要分组或不需要分组的结果集中的任何数字字段.查看来自聚合函数的值仅意味着您需要调整查询逻辑以将 GROUP BY 用于正确的字段.该方法需要在查询中创建内嵌视图，并将其用作进行减法运算的数据源.

The challenge is to get the results of one query to link over to another query all inside the same query and then manipulate the returned value of a field so that the value in a given field in one query's resultset, call it FieldA, is subtracted from the value in a field in a different resultset, call it FieldB. It doesn't matter if the subject values are the result of an aggregation function like COUNT(...); they could be any numeric field in a resultset needing grouping or not. Looking at values from aggregation functions just means you need to adjust your query logic to use GROUP BY for the proper fields. The approach requires creating in-line views in the query and using those as the source of data for doing the subtraction.

处理这种事情时的一个红鲱鱼是 MINUS 运算符(假设您使用的是 Oracle 数据库)，但这将不起作用，因为 MINUS 不是将结果集的字段值中的值相互减去，而是减去一个在查询返回的最终结果集中的另一组记录中找到的一组匹配记录.此外，MINUS 不是 SQL 标准运算符，因此如果您使用的不是 Oracle，您的数据库可能不会支持它.不过，在你需要的时候在身边真是太好了.

A red herring when dealing with this kind of thing is the MINUS operator (assuming you are using an Oracle database) but that will not work since MINUS is not about subtracting values inside a resultset's field values from one another, but subtracting one set of matching records found in another set of records from the final result set returned from the query. In addition, MINUS is not a SQL standard operator so your database probably won't support it if it isn't Oracle you are using. Still, it's awfully nice to have around when you need it.

好的，足够的前奏.这是您将要使用的查询表单，例如我们希望按 YYYY-MM 分组的日期范围:

OK, enough prelude. Here's the query form you will want to use, taking for example a date range we want grouped by YYYY-MM:

select inlineview1.year_mon, (inlineview1.CNT - inlineview2.CNT) as finalcnt from
 (SELECT TO_CHAR(*date_field*, 'YYYY-MM') AS year_mon, count(*any_field_name*) as CNT
  FROM *schemaname.tablename*
   WHERE *date_field* > TO_DATE('*{a year}-{a month}-{a day}*', 'YYYY-MM-DD') and
    *date_field* < TO_DATE('*{a year}-{a month}-{a day}*', 'YYYY-MM-DD') and 
    *another_field* = *{value_of_some_kind}* -- ... etc. ...
  GROUP BY TO_CHAR(*date_field*, 'YYYY-MM')) inlineview1,
 (SELECT TO_CHAR(*date_field*, 'YYYY-MM') AS year_mon, count(*any_field_name*) as CNT
  FROM *schemaname.tablename* 
   WHERE *date_field* > TO_DATE('*{a year}-{a month}-{a day}*', 'YYYY-MM-DD') and
     *date_field* < TO_DATE('*{a year}-{a month}-{a day}*', 'YYYY-MM-DD') and
     *another_field* = *{value_of_some_kind}* -- ... etc. ...
  GROUP BY TO_CHAR(*date_field*, 'YYYY-MM')) inlineview2
WHERE
inlineview1.year_mon = inlineview2.year_mon
order by *either or any of the final resultset's fields* -- optional

一个不太抽象的例子，其中一个书商想要查看 2013 年任何给定月份的净售书数.为此，卖家必须从售出的数量中减去退回退货的图书数量.他并不关心这本书何时被售出，因为他认为归还的书在统计上代表着销售和收入的损失，无论是何时发生与何时售出.示例:

A bit less abstractly, an example wherein a bookseller wants to see the net number of books that were sold in any given month in 2013. To do this, the seller must subtract the number of books retruned for refund from the number sold. He does not care when the book was sold, as he feels a returned book represents a loss of a sale and income statistically no matter when it occurs vs. when the book was sold. Example:

select bookssold.year_mon, (bookssold.CNT - booksreturned.CNT) as netsalescount from
 (SELECT TO_CHAR(SALE_DATE, 'YYYY-MM') AS year_mon, count(TITLE) as CNT
  FROM RETAILOPS.ACTIVITY
   WHERE SALE_DATE > TO_DATE('2012-12-31', 'YYYY-MM-DD') and
    SALE_DATE < TO_DATE('2014-01-01', 'YYYY-MM-DD') and 
    OPERATION = 'sale'
  GROUP BY TO_CHAR(SALE_DATE, 'YYYY-MM')) bookssold,
 (SELECT TO_CHAR(SALE_DATE, 'YYYY-MM') AS year_mon, count(TITLE) as CNT
  FROM RETAILOPS.ACTIVITY
   WHERE SALE_DATE > TO_DATE('2012-12-31', 'YYYY-MM-DD') and
    SALE_DATE < TO_DATE('2014-01-01', 'YYYY-MM-DD') and 
    OPERATION = 'return'
  GROUP BY TO_CHAR(SALE_DATE, 'YYYY-MM')) booksreturned
WHERE
bookssold.year_mon = booksreturned.year_mon
order by bookssold.year_mon desc

请注意，为确保查询按预期返回，两个内嵌视图必须根据某些条件如上所示进行等值连接，如下所示:

Note that to be sure the query returns as expected, the two in-line views must be equijoined based as shown above on some criteria, as in:

bookssold.year_mon = booksreturned.year_mon

或者计数记录的减法不能在 1:1 的基础上完成，因为查询解析器将不知道要从哪些返回的带有分组计数值的记录中减去.未能指定等值连接条件将产生笛卡尔连接结果，可能不是您想要的(尽管您可能确实想要那个).例如，在上例中顶层select语句的返回字段列表中，在'bookssold.year_mon'之后添加'booksreturned.year_mon'，去掉

or the subtraction of the counted records can't be done on a 1:1 basis, as the query parser will not know which of the records returned with a grouped count value is to be subtracted from which. Failing to specifiy an equijoin condition will yield a Cartesian join result, probably not what you want (though you may inded want that). For example, adding 'booksreturned.year_mon' right after 'bookssold.year_mon' to the returned fields list in the top-level select statement in the above example and eliminating the

bookssold.year_mon = booksreturned.year_mon

WHERE 子句中的标准将生成一个工作查询，该查询对结果集前两列中 YYYY-MM 值的 CNT 值进行减法计算，并在第三列中显示它们.如果您需要，了解这一点很方便，因为如果您不仅可以在给定的原子时间范围内比较销售和退货，而且可以以 1:N 的方式在这些时间范围内进行比较，那么它在业务趋势分析中具有可靠的应用.

criteria in its WHERE clause will produce a working query that does the subtraction calculation on the CNT values for the YYYY-MM values in the first two columns of the resultset and shows them in the third column. Handy to know this if you need it, as it has solid application in business trends analysis if you can compare sales and returns not just within a given atomic timeframe but as compared across such timeframes in a 1:N fashion.

这篇关于SQL按不同的条件求和2个不同的列，然后减法和加法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！