如何同时按Hour和Post分组查询数据并在HTML中展示结果？

Hey there! Let's tackle this problem step by step—first writing the right SQL query to group your data by hour and post, then rendering that result into a clean HTML table. I'll include examples for common databases and front-end/back-end scenarios.

Part 1: SQL Query to Group by Hour and Post #

First, let's assume your table (let's call it posts) has at least:

• A timestamp field (e.g., created_at) to extract the hour from
• A post identifier (e.g., post_id, post_title) to group by post

The core idea is to extract the hour component from your timestamp, then group by that hour value along with your post identifier. You'll also need an aggregate function (like COUNT(*), SUM(views)) to show metrics for each group.

Example Queries by Database #

PostgreSQL

SELECT
  DATE_TRUNC('hour', created_at) AS hour, -- Truncates timestamp to the nearest hour
  post_id,
  post_title,
  COUNT(*) AS total_interactions -- Replace with your desired metric (e.g., SUM(views))
FROM posts
GROUP BY hour, post_id, post_title -- Group by both hour and post attributes
ORDER BY hour DESC, total_interactions DESC; -- Sort for readability

MySQL/MariaDB

SELECT
  DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') AS hour, -- Formats timestamp to hour-level
  post_id,
  post_title,
  COUNT(*) AS total_interactions
FROM posts
GROUP BY hour, post_id, post_title
ORDER BY hour DESC, total_interactions DESC;

SQL Server

SELECT
  DATEADD(hour, DATEDIFF(hour, 0, created_at), 0) AS hour, -- Rounds to the start of the hour
  post_id,
  post_title,
  COUNT(*) AS total_interactions
FROM posts
GROUP BY DATEADD(hour, DATEDIFF(hour, 0, created_at), 0), post_id, post_title
ORDER BY hour DESC, total_interactions DESC;

Note: If post_id is the primary key for your posts, you can omit post_title from the GROUP BY clause in some databases (like PostgreSQL with functional dependency), but including it makes the query more compatible across systems.

Part 2: Displaying Results in HTML #

Once you've fetched the query results from your database (via your backend language of choice), you can render them into an HTML table. Below are examples for common scenarios:

Basic HTML Table (Backend Template Example) #

Here's a PHP example using a template loop—adapt this to your stack (Django/Jinja2, EJS, etc.):

<table style="border-collapse: collapse; width: 100%;">
  <thead>
    <tr style="background-color: #f0f0f0;">
      <th style="border: 1px solid #ddd; padding: 8px;">Hour</th>
      <th style="border: 1px solid #ddd; padding: 8px;">Post ID</th>
      <th style="border: 1px solid #ddd; padding: 8px;">Post Title</th>
      <th style="border: 1px solid #ddd; padding: 8px;">Total Interactions</th>
    </tr>
  </thead>
  <tbody>
    <?php foreach ($queryResults as $row): ?>
    <tr>
      <td style="border: 1px solid #ddd; padding: 8px;"><?= date('Y-m-d H:i', strtotime($row['hour'])) ?></td>
      <td style="border: 1px solid #ddd; padding: 8px;"><?= $row['post_id'] ?></td>
      <td style="border: 1px solid #ddd; padding: 8px;"><?= htmlspecialchars($row['post_title']) ?></td>
      <td style="border: 1px solid #ddd; padding: 8px; text-align: center;"><?= $row['total_interactions'] ?></td>
    </tr>
    <?php endforeach; ?>
  </tbody>
</table>

Advanced: Merged Hour Cells (Frontend JavaScript) #

If you want a cleaner table where the same hour is merged into a single cell (like a grouped view), use JavaScript to process the results first:

<table style="border-collapse: collapse; width: 100%;">
  <thead>
    <tr style="background-color: #f0f0f0;">
      <th style="border: 1px solid #ddd; padding: 8px;">Hour</th>
      <th style="border: 1px solid #ddd; padding: 8px;">Post ID</th>
      <th style="border: 1px solid #ddd; padding: 8px;">Post Title</th>
      <th style="border: 1px solid #ddd; padding: 8px;">Total Interactions</th>
    </tr>
  </thead>
  <tbody id="resultsBody"></tbody>
</table>

<script>
// Assume this data comes from your API/backend
const queryResults = [
  { hour: '2024-05-20 14:00:00', post_id: 123, post_title: 'My First Post', total_interactions: 5 },
  { hour: '2024-05-20 14:00:00', post_id: 456, post_title: 'Another Post', total_interactions: 3 },
  { hour: '2024-05-20 13:00:00', post_id: 123, post_title: 'My First Post', total_interactions: 2 }
];

// Group results by hour first
const groupedByHour = {};
queryResults.forEach(row => {
  groupedByHour[row.hour] = groupedByHour[row.hour] || [];
  groupedByHour[row.hour].push(row);
});

// Render the table
const tbody = document.getElementById('resultsBody');
Object.entries(groupedByHour).forEach(([hour, posts]) => {
  posts.forEach((post, index) => {
    const tr = document.createElement('tr');
    // Only render the hour cell once per group, using rowspan
    const hourCell = index === 0 
      ? `<td rowspan="${posts.length}" style="border: 1px solid #ddd; padding: 8px;">${new Date(hour).toLocaleString()}</td>`
      : '';
    
    tr.innerHTML = `
      ${hourCell}
      <td style="border: 1px solid #ddd; padding: 8px;">${post.post_id}</td>
      <td style="border: 1px solid #ddd; padding: 8px;">${post.post_title}</td>
      <td style="border: 1px solid #ddd; padding: 8px; text-align: center;">${post.total_interactions}</td>
    `;
    tbody.appendChild(tr);
  });
});
</script>

Key Tips:

• Always use htmlspecialchars() (or equivalent in your stack) to escape user-generated content (like post titles) to prevent XSS attacks.
• Adjust the aggregate function (COUNT(*), SUM(), etc.) to match the metric you want to display for each hour-post group.

内容的提问来源于stack exchange，提问作者Rui Pedro ИИ