添加列到单行结果集

huangapple
go评论48阅读模式
英文:

add column to single row resultset

问题

以下是查询1和查询2合并在同一结果集中的SQL代码：

SELECT 
    COUNT(1) AS batch_count,
    ROUND(MAX(batch_duration_milliseconds) / 1000) AS max_batch_duration_seconds,
    ROUND(AVG(batch_duration_milliseconds) / 1000) AS avg_batch_duration_seconds,
    ROUND(MIN(batch_duration_milliseconds) / 1000, 1) AS min_batch_duration_seconds,
    ROUND(SUM(total_queries_duration_milliseconds) / SUM(batch_duration_milliseconds) * 100) AS query_duration_percentage,
    SUM(total_queries) AS queries,
    ROUND(SUM(monitoring_queries) / SUM(total_queries) * 100) AS percent_monitoring_queries,
    ROUND(SUM(total_queries) / SUM(batch_duration_milliseconds) * 1000) AS queries_per_second,
    MAX(max_query_duration_milliseconds) AS max_query_duration,
    ROUND(AVG(avg_query_duration_milliseconds)) AS avg_query_duration,
    ROUND(SUM(retrieved_data_bytes) / 1024 / 1024) AS data_mb,
    ROUND(MAX(retrieved_data_bytes) / 1024 / 1024) AS max_batch_data_mb,
    ROUND(ROUND(AVG(retrieved_data_bytes)) / 1024 / 1024) AS avg_batch_data_mb,
    ROUND(SUM(retrieved_data_bytes_calculation_duration_milliseconds) / SUM(batch_duration_milliseconds) * 100, 1) AS percent_data_calc_duration,
    (
        SELECT ROUND(MAX(total_queries) / SUM(total_queries) * 100) 
        FROM (
            SELECT SUM(total_queries) AS total_queries
            FROM entrypoint_sql_queries
            WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR)
            GROUP BY entrypoint
        ) AS t
    ) AS percent_most_expensive_job_queries
FROM entrypoint_sql_queries
WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR);

这个SQL查询将查询1和查询2合并到同一结果集中，将查询2的结果作为名为percent_most_expensive_job_queries的额外列添加到查询1的结果中。

英文:

I have a query that gets me statistics about data in a table:

SELECT COUNT(1)                                                            batch_count,
       ROUND(MAX(batch_duration_milliseconds) / 1000)                      max_batch_duration_seconds,
       ROUND(AVG(batch_duration_milliseconds) / 1000)                      avg_batch_duration_seconds,
       ROUND(MIN(batch_duration_milliseconds) / 1000, 1)                   min_batch_duration_seconds,
       ROUND(SUM(total_queries_duration_milliseconds) / SUM(batch_duration_milliseconds) *
             100)                                                          query_duration_percentage,
       SUM(total_queries)                                                  queries,
       ROUND(SUM(monitoring_queries) / SUM(total_queries) * 100)           percent_monitoring_queries,
       ROUND(SUM(total_queries) / SUM(batch_duration_milliseconds) * 1000) queries_per_second,
       MAX(max_query_duration_milliseconds)                                max_query_duration,
       ROUND(AVG(avg_query_duration_milliseconds))                         avg_query_duration,

       ROUND(SUM(retrieved_data_bytes) / 1024 / 1024)                      data_mb,
       ROUND(MAX(retrieved_data_bytes) / 1024 / 1024)                      max_batch_data_mb,
       ROUND(ROUND(AVG(retrieved_data_bytes)) / 1024 / 1024)               avg_batch_data_mb,
       ROUND(SUM(retrieved_data_bytes_calculation_duration_milliseconds) / SUM(batch_duration_milliseconds) *
             100, 1)                                                       percent_data_calc_duration
FROM entrypoint_sql_queries
WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR);

now I want to add one more column that gets me the percent of queries caused by the entrypoint with the most queries:

  SELECT ROUND(MAX(total_queries) / SUM(total_queries) * 100) percent_most_expensive_job_queries
FROM (
         SELECT SUM(total_queries) AS total_queries
         FROM entrypoint_sql_queries
         WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR)
         GROUP BY entrypoint
     ) as t;

How can I get both into the same resultset (i.e. I want to show the resulting number of query 2 as an additional column in query 1)

答案1

得分: 0

您可以通过将第二个查询作为全局选择的单个列添加来执行：

SELECT COUNT(1)                                                            batch_count,
       ROUND(MAX(batch_duration_milliseconds) / 1000)                      max_batch_duration_seconds,
       ROUND(AVG(batch_duration_milliseconds) / 1000)                      avg_batch_duration_seconds,
       ROUND(MIN(batch_duration_milliseconds) / 1000, 1)                   min_batch_duration_seconds,
       ROUND(SUM(total_queries_duration_milliseconds) / SUM(batch_duration_milliseconds) *
             100)                                                          query_duration_percentage,
       SUM(total_queries)                                                  queries,
       ROUND(SUM(monitoring_queries) / SUM(total_queries) * 100)           percent_monitoring_queries,
       ROUND(SUM(total_queries) / SUM(batch_duration_milliseconds) * 1000) queries_per_second,
       MAX(max_query_duration_milliseconds)                                max_query_duration,
       ROUND(AVG(avg_query_duration_milliseconds))                         avg_query_duration,

       ROUND(SUM(retrieved_data_bytes) / 1024 / 1024)                      data_mb,
       ROUND(MAX(retrieved_data_bytes) / 1024 / 1024)                      max_batch_data_mb,
       ROUND(ROUND(AVG(retrieved_data_bytes)) / 1024 / 1024)               avg_batch_data_mb,
       ROUND(SUM(retrieved_data_bytes_calculation_duration_milliseconds) / SUM(batch_duration_milliseconds) *
             100, 1)                                                    percent_data_calc_duration,
       (SELECT ROUND(MAX(total_queries) / SUM(total_queries) * 100)
        FROM (
          SELECT SUM(total_queries) AS total_queries
          FROM entrypoint_sql_queries
          WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR)
          GROUP BY entrypoint
        ) as t) as percent_most_expensive_job_queries
FROM entrypoint_sql_queries
WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR);

这是您提供的SQL查询的中文翻译，其中代码部分未被翻译。

英文:

You can do it by adding your second query as a single column on the global select :

SELECT COUNT(1)                                                            batch_count,
       ROUND(MAX(batch_duration_milliseconds) / 1000)                      max_batch_duration_seconds,
       ROUND(AVG(batch_duration_milliseconds) / 1000)                      avg_batch_duration_seconds,
       ROUND(MIN(batch_duration_milliseconds) / 1000, 1)                   min_batch_duration_seconds,
       ROUND(SUM(total_queries_duration_milliseconds) / SUM(batch_duration_milliseconds) *
             100)                                                          query_duration_percentage,
       SUM(total_queries)                                                  queries,
       ROUND(SUM(monitoring_queries) / SUM(total_queries) * 100)           percent_monitoring_queries,
       ROUND(SUM(total_queries) / SUM(batch_duration_milliseconds) * 1000) queries_per_second,
       MAX(max_query_duration_milliseconds)                                max_query_duration,
       ROUND(AVG(avg_query_duration_milliseconds))                         avg_query_duration,

       ROUND(SUM(retrieved_data_bytes) / 1024 / 1024)                      data_mb,
       ROUND(MAX(retrieved_data_bytes) / 1024 / 1024)                      max_batch_data_mb,
       ROUND(ROUND(AVG(retrieved_data_bytes)) / 1024 / 1024)               avg_batch_data_mb,
       ROUND(SUM(retrieved_data_bytes_calculation_duration_milliseconds) / SUM(batch_duration_milliseconds) *
             100, 1)                                                    percent_data_calc_duration,
       ( SELECT ROUND(MAX(total_queries) / SUM(total_queries) * 100) 
           FROM (
             SELECT SUM(total_queries) AS total_queries
             FROM entrypoint_sql_queries
             WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR)
             GROUP BY entrypoint
        ) as t ) as percent_most_expensive_job_queries
FROM entrypoint_sql_queries
WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR);

huangapple
  • 本文由 发表于 2023年2月6日 18:07:35
  • 转载请务必保留本文链接：https://go.coder-hub.com/75359875.html
  • mysql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定