> ## Documentation Index > Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # プロジェクション > プロジェクションの概要、クエリパフォーマンスの向上にどのように活用できるか、およびmaterialized viewとの違いを説明するページ。 export const RunnableCode = ({children, run = false, showStats = true}) => { const [results, setResults] = useState(null); const [error, setError] = useState(null); const [loading, setLoading] = useState(false); const [showResults, setShowResults] = useState(false); const [stats, setStats] = useState(null); const [isDark, setIsDark] = useState(false); const [hoveredRow, setHoveredRow] = useState(-1); const codeRef = useRef(null); useEffect(() => { if (typeof window !== "undefined") { const check = () => setIsDark(document.documentElement.classList.contains("dark")); check(); const observer = new MutationObserver(check); observer.observe(document.documentElement, { attributes: true, attributeFilter: ["class"] }); return () => observer.disconnect(); } }, []); useEffect(() => { if (codeRef.current) { const block = codeRef.current.querySelector(".code-block"); if (block) { block.style.marginBottom = "0"; block.style.marginTop = "0"; block.style.borderBottomLeftRadius = "0"; block.style.borderBottomRightRadius = "0"; } } }); const getSqlText = () => { if (!codeRef.current) return ""; const code = codeRef.current.querySelector("code"); return (code || codeRef.current).textContent.trim(); }; const executeQuery = async () => { const sql = getSqlText(); if (!sql) return; setLoading(true); setError(null); setResults(null); setShowResults(true); try { const cleanQuery = sql.replace(/;$/, "").trim(); const params = new URLSearchParams({ query: cleanQuery, default_format: "JSONCompact", result_overflow_mode: "break", read_overflow_mode: "break", allow_experimental_analyzer: "1" }); const res = await fetch(`https://sql-clickhouse.clickhouse.com/?${params.toString()}`, { method: "POST", headers: { Authorization: `Basic ${btoa(`demo:`)}` } }); const text = await res.text(); if (!res.ok) { setError(text || `HTTP ${res.status}`); setLoading(false); return; } const json = JSON.parse(text); setResults(json); setStats(json.statistics || null); } catch (err) { setError(err.message || "クエリの実行に失敗しました"); } setLoading(false); }; useEffect(() => { if (run) executeQuery(); }, []); const formatRows = n => { if (n >= 1e9) return `${(n / 1e9).toFixed(1)}B`; if (n >= 1e6) return `${(n / 1e6).toFixed(1)}M`; if (n >= 1e3) return `${(n / 1e3).toFixed(1)}K`; return String(n); }; const formatBytes = b => { if (b >= 1e9) return `${(b / 1e9).toFixed(2)} GB`; if (b >= 1e6) return `${(b / 1e6).toFixed(2)} MB`; if (b >= 1e3) return `${(b / 1e3).toFixed(2)} KB`; return `${b} B`; }; const isNumericType = type => { return (/^(UInt|Int|Float|Decimal)/).test(type); }; const isHyperlink = value => { return typeof value === "string" && (/^https?:\/\//).test(value); }; const computeColumnExtremes = (meta, data) => { const extremes = {}; for (let i = 0; i < meta.length; i++) { if (isNumericType(meta[i].type)) { let min = Infinity, max = -Infinity; for (const row of data) { const v = Number(row[i]); if (!isNaN(v)) { if (v < min) min = v; if (v > max) max = v; } } if (max > -Infinity) { extremes[i] = { min, max }; } } } return extremes; }; const computeColumnWidths = (meta, data) => { const lengths = meta.map((col, i) => { const headerLen = col.name.length + col.type.length + 1; let maxData = 0; for (const row of data) { const v = row[i]; const len = v === null ? 4 : String(v).length; if (len > maxData) maxData = len; } return Math.max(headerLen, maxData); }); const total = lengths.reduce((s, l) => s + l, 0); return lengths.map(l => `${(l / total * 100).toFixed(1)}%`); }; const copyResultsAsTSV = () => { if (!results || !results.meta || !results.data) return; const header = results.meta.map(col => col.name).join("\t"); const rows = results.data.map(row => row.map(cell => cell === null ? "NULL" : String(cell)).join("\t")); const tsv = [header, ...rows].join("\n"); navigator.clipboard.writeText(tsv); }; const borderColor = isDark ? "rgba(255,255,255,0.15)" : "#e5e7eb"; const bgColor = isDark ? "rgba(255,255,255,0.05)" : "#f9fafb"; const headerBg = isDark ? "#2a2a2a" : "#f3f4f6"; const textColor = isDark ? "#e5e7eb" : "#1f2937"; const mutedColor = isDark ? "#d1d5db" : "#6b7280"; const accentColor = isDark ? "#FAFF69" : "#323232"; const accentTextColor = isDark ? "#000" : "#fff"; const barColor = isDark ? "#35372f" : "#d2d2d2"; const cellBg = isDark ? "#1f201b" : "#ffffff"; const cellBgHover = isDark ? "lch(15.8 0 0)" : "#f0f0f0"; const extremes = results && results.meta && results.data ? computeColumnExtremes(results.meta, results.data) : {}; const colWidths = results && results.meta && results.data ? computeColumnWidths(results.meta, results.data) : []; const getCellBarStyle = (cell, ci, ri) => { if (cell === null) return null; const colMeta = results.meta[ci]; if (!isNumericType(colMeta.type) || !extremes[ci] || results.data.length <= 1 || extremes[ci].max <= 0) return null; const ratio = 100 * Number(cell) / extremes[ci].max; const bg = ri === hoveredRow ? cellBgHover : cellBg; return { background: `linear-gradient(to right, ${barColor} 0%, ${barColor} ${ratio}%, ${bg} ${ratio}%, ${bg} 100%)` }; }; const renderCell = (cell, ci) => { if (cell === null) { return NULL; } const value = String(cell); if (isHyperlink(value)) { return {value} ; } return value; }; return
{}
{children}
{}
{results && } {showStats && stats && {formatRows(stats.rows_read)} 行、{formatBytes(stats.bytes_read)} を {stats.elapsed.toFixed(3)}s で読み取り }
{} {showResults &&
{loading &&
クエリを実行中...
} {error &&
{error}
} {results && results.meta && results.data &&
{results.meta.map((col, i) =>
1 ? "right" : "left", backgroundColor: headerBg, borderBottom: `1px solid ${borderColor}`, color: textColor, fontWeight: 600, fontSize: "12px", whiteSpace: "nowrap", overflow: "hidden", textOverflow: "ellipsis" }}> {col.name} {col.type}
)} {results.data.map((row, ri) => row.map((cell, ci) =>
setHoveredRow(ri)} onMouseLeave={() => setHoveredRow(-1)} style={{ padding: "4px 12px", color: textColor, whiteSpace: "nowrap", overflow: "hidden", textOverflow: "ellipsis", textAlign: isNumericType(results.meta[ci].type) && results.meta.length > 1 ? "right" : "left", borderBottom: `1px solid ${borderColor}`, backgroundColor: ri === hoveredRow ? cellBgHover : ri % 2 === 0 ? "transparent" : bgColor, transition: "background-color 0.1s", ...getCellBarStyle(cell, ci, ri) }}> {renderCell(cell, ci)}
))}
} {results && results.data &&
{results.rows} 行
}
}
; }; export const Image = ({img, alt, size = "lg"}) => { const normalizedSize = ["sm", "md", "lg"].includes(size) ? size : "lg"; return
{alt}
; };
## はじめに
ClickHouse には、リアルタイムなシナリオで大量のデータに対する分析クエリを高速化するための、さまざまな仕組みがあります。そうしたクエリ高速化の仕組みの 1 つが、*プロジェクション* の利用です。プロジェクションは、着目する属性に基づいてデータを並べ替えたものを作成することで、クエリの最適化に役立ちます。具体的には、次のいずれかの形を取ります。 1. 完全な並べ替え 2. 元のテーブルの一部を異なる順序で並べ替えたもの 3. 事前計算された集約 (materialized view に似ています) で、集約に合わせた順序付けが されているもの