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

ix0qys7i  于 2021-07-24  发布在  Java
关注(0)|答案(4)|浏览(215)

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

  1. transaction_reference status time_stamp
  2. 123av buy 2020-07-23 06:06:17
  3. 123av sell 2020-07-23 06:06:18
  4. 124av buy 2020-07-23 06:06:23
  5. 124av sell 2020-07-23 06:06:38
  6. 125cv buy 2020-07-23 06:06:24
  7. 125av buy 2020-07-23 06:06:14
  8. 125ad paid 2020-07-23 06:06:14

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

  1. SELECT
  2. count(DISTINCT transaction_reference),transaction_reference, MAX(time_stamp),status
  3. FROM
  4. transactiontable
  5. WHERE
  6. time_stamp >= '2020-07-23 06:00:00' and time_stamp <= '2020-07-23 16:00:00'
  7. GROUP BY
  8. transaction_reference;

因此我得到以下结果:

  1. transaction_reference status time_stamp
  2. 123av sell 2020-07-23 06:06:18
  3. 124av sell 2020-07-23 06:06:38
  4. 125cv buy 2020-07-23 06:06:24
  5. 125av buy 2020-07-23 06:06:14
  6. 125ad paid 2020-07-23 06:06:14

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

  1. Select count(*),C from table1
  2. where
  3. (select * from table1 where a='12')
  4. group by C;
mwg9r5ms

mwg9r5ms1#

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

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

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

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

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

  1. SELECT
  2. count(DISTINCT transaction_reference),transaction_reference, MAX(time_stamp)
  3. sum(case when status='a' then 1 else 0 end) as count_a_status,
  4. sum(case when status='b' then 1 else 0 end) as count_b_status,
  5. sum(case when status='c' then 1 else 0 end) as count_c_status
  6. FROM
  7. transactiontable
  8. WHERE
  9. time_stamp >= '2020-07-23 06:00:00' and time_stamp <= '2020-07-23 16:00:00'
  10. GROUP BY
  11. transaction_reference;
展开查看全部
pxy2qtax

pxy2qtax2#

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

  1. SELECT bt.*
  2. FROM dxl_payment.BARCLAYS_TRANSACTION bt
  3. WHERE bt.time_stamp = (SELECT MAX(bt2.time_stamp)
  4. FROM dxl_payment.BARCLAYS_TRANSACTION bt2
  5. WHERE bt2.transaction_reference = bt.transaction_reference AND
  6. DATE(bt2.time_stamp) = DATE(bt.time_stamp)
  7. ) AND
  8. bt.time_stamp >= '2020-07-23 06:00:00' AND
  9. bt.time_stamp <= '2020-07-23 16:00:00'

要获取状态分布,请聚合:

  1. SELECT bt.status, COUNT(*)
  2. FROM dxl_payment.BARCLAYS_TRANSACTION bt
  3. WHERE bt.time_stamp = (SELECT MAX(bt2.time_stamp)
  4. FROM dxl_payment.BARCLAYS_TRANSACTION bt2
  5. WHERE bt2.transaction_reference = bt.transaction_reference AND
  6. DATE(bt2.time_stamp) = DATE(bt.time_stamp) AND
  7. bt2.time_stamp >= '2020-07-23 06:00:00' AND
  8. bt2.time_stamp <= '2020-07-23 16:00:00'
  9. ) AND
  10. bt.time_stamp >= '2020-07-23 06:00:00' AND
  11. bt.time_stamp <= '2020-07-23 16:00:00'
  12. GROUP BY bt.status;

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

展开查看全部
yi0zb3m4

yi0zb3m43#

正在使用您的查询:

  1. SELECT
  2. count(DISTINCT transaction_reference),transaction_reference, MAX(time_stamp),status
  3. FROM
  4. transactiontable
  5. WHERE
  6. time_stamp >= '2020-07-23 06:00:00' and time_stamp <= '2020-07-23 16:00:00'
  7. GROUP BY
  8. transaction_reference;

Package 如下:

  1. select
  2. /* remove DISTINCT below to get pure count/total */
  3. count(DISTINCT status) as status_count,
  4. sum(case when status = 'buy' then 1 else 0 end) as buy,
  5. sum(case when status = 'sell' then 1 else 0 end) as sell,
  6. sum(case when status = 'paid' then 1 else 0 end) as paid
  7. from (
  8. SELECT
  9. count(DISTINCT transaction_reference),transaction_reference, MAX(time_stamp),status
  10. FROM
  11. transactiontable
  12. WHERE
  13. time_stamp >= '2020-07-23 06:00:00' and time_stamp <= '2020-07-23 16:00:00'
  14. GROUP BY
  15. transaction_reference
  16. ) your_query;
展开查看全部
8hhllhi2

8hhllhi24#

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

  1. select count(distinct a.transaction_reference),a.status from transactiontable as a ,
  2. (
  3. SELECT
  4. count(DISTINCT transaction_reference),transaction_reference, MAX(time_stamp) as time_stamp,status
  5. FROM
  6. transactiontable
  7. WHERE
  8. time_stamp >= '2020-07-24 16:00:00' and time_stamp <= '2020-07-25 16:00:00'
  9. GROUP BY
  10. transaction_reference) as b where a.transaction_reference=b.transaction_reference and a.time_stamp=b.time_stamp
  11. group by a.status;

这给了我确切的数字。

相关问题