我想在得到查询结果后按查询运行group

ix0qys7i 于 2021-07-24 发布在 Java

关注(0)|答案(4)|浏览(183)

我对sql有一个基本的了解（不擅长连接），所以我要写一个脚本，在这个脚本中我想得到事务的最近状态的总数。
i、在一天结束时，状态的计数是多少，买了多少，卖了多少，付了多少，等等。
例如：

transaction_reference  status   time_stamp
123av                   buy     2020-07-23 06:06:17
123av                   sell    2020-07-23 06:06:18
124av                   buy     2020-07-23 06:06:23
124av                   sell    2020-07-23 06:06:38
125cv                   buy     2020-07-23 06:06:24
125av                   buy     2020-07-23 06:06:14
125ad                   paid     2020-07-23 06:06:14

我得到了一个查询来获取每笔交易的最后状态，

SELECT
  count(DISTINCT transaction_reference),transaction_reference, MAX(time_stamp),status
FROM
  transactiontable
WHERE
    time_stamp >= '2020-07-23 06:00:00' and time_stamp <= '2020-07-23 16:00:00'
GROUP BY
transaction_reference;

因此我得到以下结果：

transaction_reference  status   time_stamp
123av                   sell    2020-07-23 06:06:18
124av                   sell    2020-07-23 06:06:38
125cv                   buy     2020-07-23 06:06:24
125av                   buy     2020-07-23 06:06:14
125ad                   paid    2020-07-23 06:06:14

这个结果给了我每个交易的最新状态\参考，现在我要计算状态，*状态除了买入和卖出还有其他值。
这是所需的输出：
状态计数买2卖3付1
因为我在“join”方面很弱，所以我的第一个解决方案是将其放入临时表中，然后通过命令运行组。
另一种是使用内部连接。
如果这可以通过使用连接来实现，有人能告诉我怎么做吗？
或者简单地说，我可以运行query-on-query，
喜欢

Select count(*),C from table1
where
(select * from table1 where a='12')
group by C;

sql mysql

来源：https://stackoverflow.com/questions/63072881/i-want-to-run-a-group-by-query-after-i-get-a-result-of-a-query

4条答案

按热度按时间

mwg9r5ms1#

也只是按状态分组。。。这样地：

SELECT
  count(*), transaction_reference, MAX(time_stamp),status
FROM
  dxl_payment.BARCLAYS_TRANSACTION 
WHERE
    time_stamp >= '2020-07-23 06:00:00' and time_stamp <= '2020-07-23 16:00:00'
GROUP BY transaction_reference, status

现在您将获得每个状态类型的一行以及这些类型的计数
要获取每个状态的计数，请执行以下操作：

SELECT
  count(*), status
FROM
  dxl_payment.BARCLAYS_TRANSACTION 
WHERE
    time_stamp >= '2020-07-23 06:00:00' and time_stamp <= '2020-07-23 16:00:00'
GROUP BY status

基于另一组得到计数的方法是使用sum——如下所示：

SELECT
  count(DISTINCT transaction_reference),transaction_reference, MAX(time_stamp)
  sum(case when status='a' then 1 else 0 end) as count_a_status,
  sum(case when status='b' then 1 else 0 end) as count_b_status,
  sum(case when status='c' then 1 else 0 end) as count_c_status

FROM
  transactiontable
WHERE
    time_stamp >= '2020-07-23 06:00:00' and time_stamp <= '2020-07-23 16:00:00'
GROUP BY
transaction_reference;

赞(0）回复(0）举报 2021-07-24

pxy2qtax2#

您可以使用时间比较和相关子查询获取每天的最后状态：

SELECT bt.*
FROM dxl_payment.BARCLAYS_TRANSACTION bt
WHERE bt.time_stamp = (SELECT MAX(bt2.time_stamp)
                       FROM dxl_payment.BARCLAYS_TRANSACTION bt2
                       WHERE bt2.transaction_reference = bt.transaction_reference AND
                             DATE(bt2.time_stamp) = DATE(bt.time_stamp)
                      ) AND
      bt.time_stamp >= '2020-07-23 06:00:00' AND
      bt.time_stamp <= '2020-07-23 16:00:00'

要获取状态分布，请聚合：

SELECT bt.status, COUNT(*)
FROM dxl_payment.BARCLAYS_TRANSACTION bt
WHERE bt.time_stamp = (SELECT MAX(bt2.time_stamp)
                       FROM dxl_payment.BARCLAYS_TRANSACTION bt2
                       WHERE bt2.transaction_reference = bt.transaction_reference AND
                             DATE(bt2.time_stamp) = DATE(bt.time_stamp) AND
                             bt2.time_stamp >= '2020-07-23 06:00:00' AND
                             bt2.time_stamp <= '2020-07-23 16:00:00'
                      ) AND
      bt.time_stamp >= '2020-07-23 06:00:00' AND
      bt.time_stamp <= '2020-07-23 16:00:00'
GROUP BY bt.status;

为了提高性能，您需要一个索引 BARCLAYS_TRANSACTION(transaction_reference, time_stamp) .

赞(0）回复(0）举报 2021-07-24

yi0zb3m43#

正在使用您的查询：

SELECT
  count(DISTINCT transaction_reference),transaction_reference, MAX(time_stamp),status
FROM
  transactiontable
WHERE
    time_stamp >= '2020-07-23 06:00:00' and time_stamp <= '2020-07-23 16:00:00'
GROUP BY
transaction_reference;

Package 如下：

select
  /* remove DISTINCT below to get pure count/total */
  count(DISTINCT status) as status_count,
  sum(case when status = 'buy' then 1 else 0 end) as buy,
  sum(case when status = 'sell' then 1 else 0 end) as sell,
  sum(case when status = 'paid' then 1 else 0 end) as paid
from (
  SELECT
    count(DISTINCT transaction_reference),transaction_reference, MAX(time_stamp),status
  FROM
    transactiontable
  WHERE
      time_stamp >= '2020-07-23 06:00:00' and time_stamp <= '2020-07-23 16:00:00'
  GROUP BY
  transaction_reference
) your_query;

赞(0）回复(0）举报 2021-07-24

8hhllhi24#

所以我花了2个小时学习加入，现在，这是工作100%。
记录是否有人认为这是有用的。

select count(distinct a.transaction_reference),a.status from transactiontable as a ,
(
SELECT
  count(DISTINCT transaction_reference),transaction_reference, MAX(time_stamp) as time_stamp,status
FROM
   transactiontable
WHERE
    time_stamp >= '2020-07-24 16:00:00' and time_stamp <= '2020-07-25 16:00:00'
GROUP BY
transaction_reference) as b where a.transaction_reference=b.transaction_reference  and  a.time_stamp=b.time_stamp 
group by a.status;

这给了我确切的数字。

赞(0）回复(0）举报 2021-07-24

我来回答

我想在得到查询结果后按查询运行group

4条答案

相关问题

热门标签

最新问答