英文:
How to obtain the size in bytes of a certain specific rows of a table
问题
I have created a project where I will offer several stores the opportunity to publish their products and charge them based on the Megabytes they occupy in storage. For this purpose, I am using an SQL database. My question is, how can I obtain the space occupied by a customer in my database if the "products" table has the following columns: "productId", "customerId", "productName", "productPrice"?
I have tried this but it returns the total size of the entire table and what I want is to get the size of certain rows where "customerId" is equal to the requested.
SELECT (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) AS 'bytes'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'project' AND TABLE_NAME = 'products';
If you have a better idea to solve this I would greatly appreciate it.
英文:
I have created a project where I will offer several stores the opportunity to publish their products and charge them based on the Megabytes they occupy in storage. For this purpose, I am using an SQL database. My question is, how can I obtain the space occupied by a customer in my database if the "products" table has the following columns: "productId", "customerId", "productName", "productPrice"?
I have tried this but it returns the total size of the entire table and what I want is to get the size of certain rows where "customerId" is equal to the requested.
SELECT (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) AS 'bytes'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'project' AND TABLE_NAME = 'products';
If you have a better idea to solve this I would greatly appreciate it.
答案1
得分: 0
以下是翻译好的部分:
如果我们可以考虑行都是相同大小的话,这个方法可能会起作用:
思路是统计每个客户的行数,除以总行数,然后将结果乘以总字节数。
with cte as (
select count(1) as totalData, s.bytes
from products
inner join (
SELECT (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) AS 'bytes'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'project' and TABLE_NAME = 'products'
) as s on true
)
select customer_id, (count(1)/totalData)*c.bytes as totalBytes
from products
inner join cte c on true
group by customer_id
英文:
If we can consider that the rows are all the same size, this could work:
The idea is to count the rows per customer, divide by the total number of rows, and multiply the result by the total number of bytes.
with cte as (
select count(1) as totalData, s.bytes
from products
inner join (
SELECT (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) AS 'bytes'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'project' and TABLE_NAME = 'products'
) as s on true
)
select customer_id, (count(1)/totalData)*c.bytes as totalBytes
from products
inner join cte c on true
group by customer_id
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论