SELECT domain_name, http_port, DATE_FORMAT(FROM_UNIXTIME(TIME), '%Y-%m-%d') AS day, COUNT(*) AS count, SUM(ll) / (1024 * 1024) AS ll, SUM(llsd) / (1024 * 1024) AS llsd, SUM(cksj) / (1024 * 1024) AS cksj FROM video_traffic_record GROUP BY domain_name, http_port, day ORDER BY count DESC LIMIT 10 OFFSET 2;用elasticsearch怎么写？

你的 SQL 查询可以转换为 Elasticsearch 的聚合查询。下面是一种可能的实现方式：

GET video_traffic_record/_search
{
  "size": 0,
  "query": {
    "match_all": {}
  },
  "aggs": {
    "domains": {
      "terms": {
        "field": "domain_name",
        "size": 10,
        "order": {
          "count": "desc"
        }
      },
      "aggs": {
        "ports": {
          "terms": {
            "field": "http_port"
          },
          "aggs": {
            "days": {
              "date_histogram": {
                "field": "TIME",
                "interval": "day",
                "format": "yyyy-MM-dd"
              },
              "aggs": {
                "count": {
                  "value_count": {
                    "field": "TIME"
                  }
                },
                "ll": {
                  "sum": {
                    "field": "ll"
                  }
                },
                "llsd": {
                  "sum": {
                    "field": "llsd"
                  }
                },
                "cksj": {
                  "sum": {
                    "field": "cksj"
                  }
                }
              }
            }
          }
        },
        "count": {
          "sum_bucket": {
            "buckets_path": "ports>days>count"
          }
        },
        "ll": {
          "sum_bucket": {
            "buckets_path": "ports>days>ll"
          },
          "bucket_script": {
            "buckets_path": {
              "sum": "ll"
            },
            "script": "params.sum / (1024 * 1024)"
          }
        },
        "llsd": {
          "sum_bucket": {
            "buckets_path": "ports>days>llsd"
          },
          "bucket_script": {
            "buckets_path": {
              "sum": "llsd"
            },
            "script": "params.sum / (1024 * 1024)"
          }
        },
        "cksj": {
          "sum_bucket": {
            "buckets_path": "ports>days>cksj"
          },
          "bucket_script": {
            "buckets_path": {
              "sum": "cksj"
            },
            "script": "params.sum / (1024 * 1024)"
          }
        }
      }
    }
  }
}

这个查询将会：

- 按 `domain_name` 分组，选择前 10 个分组，并按 `count` 排序。
- 在每个 `domain_name` 分组中，按 `http_port` 分组。
- 在每个 `http_port` 分组中，按日期分桶（每天一个桶），并计算 `count`、`ll`、`llsd` 和 `cksj` 的总和。
- 计算每个 `domain_name` 分组的总 `count`、`ll`、`llsd` 和 `cksj`。

注意，这个查询假设 `TIME` 字段是一个 Unix 时间戳。如果你的时间字段格式不同，需要修改 `date_histogram` 聚合的 `format` 参数。