时间戳

首页 > 解决方案 > 为什么下面的查询没有返回结果？

sql - 为什么下面的查询没有返回结果？

问题描述

SQL 查询可以在此链接上找到 https://cloud.google.com/kubernetes-engine/docs/how-to/cluster-usage-metering#expandable-1-label

即使 Billing dataset & table 以及 GKE 使用计量 dataset & table 没有问题。

SELECT
  resource_usage.cluster_name,
  resource_usage.cluster_location,
  resource_usage.namespace,
  resource_usage.resource_name,
  resource_usage.sku_id,
  MIN(resource_usage.start_time) AS usage_start_time,
  MAX(resource_usage.end_time) AS usage_end_time,
  SUM(resource_usage.usage.amount * gcp_billing_export.rate) AS cost
FROM
  'cluster-gcp-project.usage-metering-dataset.gke_cluster_resource_usage' AS resource_usage
LEFT JOIN (
  SELECT
    sku.id AS sku_id,
    SUM(cost) / SUM(usage.amount) AS rate,
    MIN(usage_start_time) AS min_usage_start_time,
    MAX(usage_end_time) AS max_usage_end_time
  FROM
    'cluster-gcp-project.billing-dataset.billing-table'
  WHERE
    project.id = "cluster-gcp-project"
  GROUP BY
    sku_id) AS gcp_billing_export
ON
  resource_usage.sku_id = gcp_billing_export.sku_id
WHERE
  resource_usage.start_time >= gcp_billing_export.min_usage_start_time
  AND resource_usage.end_time <= gcp_billing_export.max_usage_end_time
GROUP BY
  resource_usage.cluster_name,
  resource_usage.cluster_location,
  resource_usage.namespace,
  resource_usage.resource_name,
  resource_usage.sku_id

标签： sqlgoogle-cloud-platformgoogle-bigquerygoogle-kubernetes-engine

解决方案

我发现了一个问题，它与该查询中的 WHERE 条件有关

WHERE
  resource_usage.start_time >= gcp_billing_export.min_usage_start_time
  AND resource_usage.end_time <= gcp_billing_export.max_usage_end_time

如您所见，以下条件失败，因此不会返回任何结果。

仅供参考，逻辑是检查和验证两个不同表的 start_time 和 end_time 并返回值。

谢谢大家的回复。

推荐阅读