MySQL InnoDB查询性能(MySQL InnoDB query performance)
我试图优化一个简单的SQL查询，这个查询将在很多次的数据上运行。
这是场景：
- 带InnoDB表的MySQL
- 在何处和联接中使用的所有字段都是索引的。 表格有FK的。
- 我不需要整个查询缓存，但是每个表的缓存都是可能的。
- 表格有更多的读取更新/插入。
一个真正的问题：
SELECT one_field, another_field FROM big_table INNER JOIN medium_table ON ( ... ) INNER JOIN small_table ON ( ... ) WHERE week >= #number AND week <= #number AND year >= #number AND year <= #number AND medium_table.indexed_field = #number AND small_table.pk= #number一些测试结果：
- 平均查询时间：2,3374秒
- 测试表包含真实表的第十部分数据（或更少），比如big_test_table的行数为75.000。
- 查询返回（在测试用例中）6500行。
- Navicat表示，99.95％的查询时间用于“发送数据”
- 我用MyISAM试过了，平均查询时间是：21989秒
我该如何改进查询或数据库方案？
这是更好的连接我的年/周领域，并在[yearweek]索引领域？
在一些基准测试中，我发现MyISAM比Innodb快，但看起来我有一点改进：
http://www.mysqlperformanceblog.com/files/benchmarks/innodb-myisam-falcon.html http://www.jortk.nl/2008/12/mysql-performance-benchmark-myisam-versus-innodb/是否值得失去FK并去MyISAM？
谢谢！
更新：
选择的解释：
1 SIMPLE small_table const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE medium_table index_merge PRIMARY,Fk_small_table, Fk_indexed_field Fk_small_table, Fk_indexed_field 5,5 250 Using intersect(Fk_small_table, Fk_indexed_field); Using where 1 SIMPLE big_table ref Fk_code_of_medium_table,week-year-compound-index Fk_three 5 medium_table.primary_key 4 Using where数据库模式：
CREATE TABLE IF NOT EXISTS `db`.`big_table` ( `primary_key` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , `code_of_medium_table` INT UNSIGNED NULL , `week` SMALLINT UNSIGNED NULL , `year` YEAR NULL , ...... PRIMARY KEY (`primary_key`) , INDEX `Fk_code_of_medium_table` (`code_of_medium_table` ASC) , INDEX `In_week-year` (`week` ASC, `year` ASC) , CONSTRAINT `Fk_code_of_medium_table` FOREIGN KEY (`code_of_medium_table` ) REFERENCES `db`.`medium_table` (`code` ) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB CREATE TABLE IF NOT EXISTS `db`.`medium_table` ( `primary_key` INT UNSIGNED NOT NULL AUTO_INCREMENT , ..... `code_of_small_table` INT UNSIGNED NULL , `indexed_field ` INT UNSIGNED NULL , PRIMARY KEY (`primary_key`) , INDEX `Fk_code_of_small_table` (`code_of_small_table` ASC) , INDEX `Fk_indexed_field` (`another_field` ASC) , CONSTRAINT `Fk_code_of_small_table` FOREIGN KEY (`code_of_small_table` ) REFERENCES `db`.`small_table` (`code` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Fk_indexed_field ` FOREIGN KEY (`indexed_field ` ) REFERENCES `db`.`other_table` (`code` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB CREATE TABLE IF NOT EXISTS `db`.`small_table` ( `primary_key` INT UNSIGNED NOT NULL AUTO_INCREMENT , ..... PRIMARY KEY (`primary_key`) ) ENGINE = InnoDB变量输出：
auto_increment_increment 1 auto_increment_offset 1 automatic_sp_privileges ON back_log 50 basedir /usr/ bdb_cache_size 8384512 bdb_home /var/lib/mysql/ bdb_log_buffer_size 262144 bdb_logdir bdb_max_lock 10000 bdb_shared_data OFF bdb_tmpdir /tmp/ binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set_client utf8 character_set_connection utf8 character_set_database latin1 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection utf8_general_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci completion_type 0 concurrent_insert 1 connect_timeout 10 datadir /var/lib/mysql/ date_format %Y-%m-%d datetime_format %Y-%m-%d %H:%i:%s default_week_format 0 delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 div_precision_increment 4 keep_files_on_create OFF engine_condition_pushdown OFF expire_logs_days 0 flush OFF flush_time 0 ft_boolean_syntax + -><()~*:""&| ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (built-in) group_concat_max_len 1024 have_archive NO have_bdb YES have_blackhole_engine NO have_compress YES have_crypt YES have_csv NO have_dynamic_loading YES have_example_engine NO have_federated_engine NO have_geometry YES have_innodb YES have_isam NO have_merge_engine YES have_ndbcluster NO have_openssl DISABLED have_ssl DISABLED have_query_cache YES have_raid NO have_rtree_keys YES have_symlink YES hostname EarnedBE002 init_connect init_file init_slave innodb_additional_mem_pool_size 1048576 innodb_autoextend_increment 8 innodb_buffer_pool_awe_mem_mb 0 innodb_buffer_pool_size 8388608 innodb_checksums ON innodb_commit_concurrency 0 innodb_concurrency_tickets 500 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_adaptive_hash_index ON innodb_doublewrite ON innodb_fast_shutdown 1 innodb_file_io_threads 4 innodb_file_per_table OFF innodb_flush_log_at_trx_commit 1 innodb_flush_method innodb_force_recovery 0 innodb_lock_wait_timeout 50 innodb_locks_unsafe_for_binlog OFF innodb_log_arch_dir innodb_log_archive OFF innodb_log_buffer_size 1048576 innodb_log_file_size 5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_max_dirty_pages_pct 90 innodb_max_purge_lag 0 innodb_mirrored_log_groups 1 innodb_open_files 300 innodb_rollback_on_timeout OFF innodb_support_xa ON innodb_sync_spin_loops 20 innodb_table_locks ON innodb_thread_concurrency 8 innodb_thread_sleep_delay 10000 interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 8384512 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 language /usr/share/mysql/english/ large_files_support ON large_page_size 0 large_pages OFF lc_time_names en_US license GPL local_infile ON locked_in_memory OFF log OFF log_bin OFF log_bin_trust_function_creators OFF log_error log_queries_not_using_indexes OFF log_slave_updates OFF log_slow_queries OFF log_warnings 1 long_query_time 10 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 0 max_allowed_packet 1048576 max_binlog_cache_size 18446744073709547520 max_binlog_size 1073741824 max_connect_errors 10 max_connections 100 max_delayed_threads 20 max_error_count 64 max_heap_table_size 16777216 max_insert_delayed_threads 20 max_join_size 18446744073709551615 max_length_for_sort_data 1024 max_prepared_stmt_count 16382 max_relay_log_size 0 max_seeks_for_key 18446744073709551615 max_sort_length 1024 max_sp_recursion_depth 0 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 18446744073709551615 multi_range_count 256 myisam_data_pointer_size 6 myisam_max_sort_file_size 9223372036853727232 myisam_recover_options OFF myisam_repair_threads 1 myisam_sort_buffer_size 8388608 myisam_stats_method nulls_unequal net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF old_passwords OFF open_files_limit 1024 optimizer_prune_level 1 optimizer_search_depth 62 pid_file /var/run/mysqld/mysqld.pid plugin_dir port 3306 preload_buffer_size 32768 profiling OFF profiling_history_size 15 protocol_version 10 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 0 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 range_alloc_block_size 4096 read_buffer_size 131072 read_only OFF read_rnd_buffer_size 262144 relay_log relay_log_index relay_log_info_file relay-log.info relay_log_purge ON relay_log_space_limit 0 rpl_recovery_rank 0 secure_auth OFF secure_file_priv server_id 0 skip_external_locking ON skip_networking OFF skip_show_database OFF slave_compressed_protocol OFF slave_load_tmpdir /tmp/ slave_net_timeout 3600 slave_skip_errors OFF slave_transaction_retries 10 slow_launch_time 2 socket /var/lib/mysql/mysql.sock sort_buffer_size 2097144 sql_big_selects ON sql_mode sql_notes ON sql_warnings OFF ssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine MyISAM sync_binlog 0 sync_frm ON system_time_zone UTC table_cache 64 table_lock_wait_timeout 50 table_type MyISAM thread_cache_size 0 thread_stack 262144 time_format %H:%i:%s time_zone SYSTEM timed_mutexes OFF tmp_table_size 33554432 tmpdir /tmp/ transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ updatable_views_with_limit YES version 5.0.77 version_bdb Sleepycat Software: Berkeley DB 4.1.24: (January 29, 2009) version_comment Source distribution version_compile_machine x86_64 version_compile_os redhat-linux-gnu wait_timeout 28800
随着ypercube提出的索引，此刻，这是最快的查询（2,107s avg）：
SELECT SQL_NO_CACHE int_field, varchar_field FROM big_table INNER JOIN medium_table ON ( ... ) INNER JOIN small_table ON ( ... ) WHERE week BETWEEN #number AND #number AND year BETWEEN #number AND #number AND small_table.pk = 248 AND medium_table.indexed_field = #numberI'm trying to optimize a simple sql query that will be run over a ton of data lot of times.
This is the scenario:
- MySQL with InnoDB tables
- All fields used in where and join are Indexed. Tables have FK's.
- I don't need a whole cache of the query, but a cache of each table it's possible.
- Tables have much more reads that updates / inserts.
A real query:
SELECT one_field, another_field FROM big_table INNER JOIN medium_table ON ( ... ) INNER JOIN small_table ON ( ... ) WHERE week >= #number AND week <= #number AND year >= #number AND year <= #number AND medium_table.indexed_field = #number AND small_table.pk= #numberSome test results:
- Average query time: 2,3374s
- Test tables contains the tenth part of data (or even less) that real tables, like 75.000 rows for big_test_table.
- Query returns (in test cases) 6500 rows.
- Navicat says that 99.95% of query time is spended on "Sending data"
- I've tried with MyISAM, and the average query time was: 2,1898s
How can I improve the query or the database scheme?
It's better concatenate my year / week fields and in a [yearweek] indexed field?
In some benchmarks I see that MyISAM is faster than Innodb, but seems I have a little improvement:
http://www.mysqlperformanceblog.com/files/benchmarks/innodb-myisam-falcon.html http://www.jortk.nl/2008/12/mysql-performance-benchmark-myisam-versus-innodb/Is it worth lose FKs and go to MyISAM?
Thanks!
Updates:
Explain of the select:
1 SIMPLE small_table const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE medium_table index_merge PRIMARY,Fk_small_table, Fk_indexed_field Fk_small_table, Fk_indexed_field 5,5 250 Using intersect(Fk_small_table, Fk_indexed_field); Using where 1 SIMPLE big_table ref Fk_code_of_medium_table,week-year-compound-index Fk_three 5 medium_table.primary_key 4 Using whereDB schema:
CREATE TABLE IF NOT EXISTS `db`.`big_table` ( `primary_key` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , `code_of_medium_table` INT UNSIGNED NULL , `week` SMALLINT UNSIGNED NULL , `year` YEAR NULL , ...... PRIMARY KEY (`primary_key`) , INDEX `Fk_code_of_medium_table` (`code_of_medium_table` ASC) , INDEX `In_week-year` (`week` ASC, `year` ASC) , CONSTRAINT `Fk_code_of_medium_table` FOREIGN KEY (`code_of_medium_table` ) REFERENCES `db`.`medium_table` (`code` ) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB CREATE TABLE IF NOT EXISTS `db`.`medium_table` ( `primary_key` INT UNSIGNED NOT NULL AUTO_INCREMENT , ..... `code_of_small_table` INT UNSIGNED NULL , `indexed_field ` INT UNSIGNED NULL , PRIMARY KEY (`primary_key`) , INDEX `Fk_code_of_small_table` (`code_of_small_table` ASC) , INDEX `Fk_indexed_field` (`another_field` ASC) , CONSTRAINT `Fk_code_of_small_table` FOREIGN KEY (`code_of_small_table` ) REFERENCES `db`.`small_table` (`code` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Fk_indexed_field ` FOREIGN KEY (`indexed_field ` ) REFERENCES `db`.`other_table` (`code` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB CREATE TABLE IF NOT EXISTS `db`.`small_table` ( `primary_key` INT UNSIGNED NOT NULL AUTO_INCREMENT , ..... PRIMARY KEY (`primary_key`) ) ENGINE = InnoDBVariables output:
auto_increment_increment 1 auto_increment_offset 1 automatic_sp_privileges ON back_log 50 basedir /usr/ bdb_cache_size 8384512 bdb_home /var/lib/mysql/ bdb_log_buffer_size 262144 bdb_logdir bdb_max_lock 10000 bdb_shared_data OFF bdb_tmpdir /tmp/ binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set_client utf8 character_set_connection utf8 character_set_database latin1 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection utf8_general_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci completion_type 0 concurrent_insert 1 connect_timeout 10 datadir /var/lib/mysql/ date_format %Y-%m-%d datetime_format %Y-%m-%d %H:%i:%s default_week_format 0 delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 div_precision_increment 4 keep_files_on_create OFF engine_condition_pushdown OFF expire_logs_days 0 flush OFF flush_time 0 ft_boolean_syntax + -><()~*:""&| ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (built-in) group_concat_max_len 1024 have_archive NO have_bdb YES have_blackhole_engine NO have_compress YES have_crypt YES have_csv NO have_dynamic_loading YES have_example_engine NO have_federated_engine NO have_geometry YES have_innodb YES have_isam NO have_merge_engine YES have_ndbcluster NO have_openssl DISABLED have_ssl DISABLED have_query_cache YES have_raid NO have_rtree_keys YES have_symlink YES hostname EarnedBE002 init_connect init_file init_slave innodb_additional_mem_pool_size 1048576 innodb_autoextend_increment 8 innodb_buffer_pool_awe_mem_mb 0 innodb_buffer_pool_size 8388608 innodb_checksums ON innodb_commit_concurrency 0 innodb_concurrency_tickets 500 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_adaptive_hash_index ON innodb_doublewrite ON innodb_fast_shutdown 1 innodb_file_io_threads 4 innodb_file_per_table OFF innodb_flush_log_at_trx_commit 1 innodb_flush_method innodb_force_recovery 0 innodb_lock_wait_timeout 50 innodb_locks_unsafe_for_binlog OFF innodb_log_arch_dir innodb_log_archive OFF innodb_log_buffer_size 1048576 innodb_log_file_size 5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_max_dirty_pages_pct 90 innodb_max_purge_lag 0 innodb_mirrored_log_groups 1 innodb_open_files 300 innodb_rollback_on_timeout OFF innodb_support_xa ON innodb_sync_spin_loops 20 innodb_table_locks ON innodb_thread_concurrency 8 innodb_thread_sleep_delay 10000 interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 8384512 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 language /usr/share/mysql/english/ large_files_support ON large_page_size 0 large_pages OFF lc_time_names en_US license GPL local_infile ON locked_in_memory OFF log OFF log_bin OFF log_bin_trust_function_creators OFF log_error log_queries_not_using_indexes OFF log_slave_updates OFF log_slow_queries OFF log_warnings 1 long_query_time 10 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 0 max_allowed_packet 1048576 max_binlog_cache_size 18446744073709547520 max_binlog_size 1073741824 max_connect_errors 10 max_connections 100 max_delayed_threads 20 max_error_count 64 max_heap_table_size 16777216 max_insert_delayed_threads 20 max_join_size 18446744073709551615 max_length_for_sort_data 1024 max_prepared_stmt_count 16382 max_relay_log_size 0 max_seeks_for_key 18446744073709551615 max_sort_length 1024 max_sp_recursion_depth 0 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 18446744073709551615 multi_range_count 256 myisam_data_pointer_size 6 myisam_max_sort_file_size 9223372036853727232 myisam_recover_options OFF myisam_repair_threads 1 myisam_sort_buffer_size 8388608 myisam_stats_method nulls_unequal net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF old_passwords OFF open_files_limit 1024 optimizer_prune_level 1 optimizer_search_depth 62 pid_file /var/run/mysqld/mysqld.pid plugin_dir port 3306 preload_buffer_size 32768 profiling OFF profiling_history_size 15 protocol_version 10 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 0 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 range_alloc_block_size 4096 read_buffer_size 131072 read_only OFF read_rnd_buffer_size 262144 relay_log relay_log_index relay_log_info_file relay-log.info relay_log_purge ON relay_log_space_limit 0 rpl_recovery_rank 0 secure_auth OFF secure_file_priv server_id 0 skip_external_locking ON skip_networking OFF skip_show_database OFF slave_compressed_protocol OFF slave_load_tmpdir /tmp/ slave_net_timeout 3600 slave_skip_errors OFF slave_transaction_retries 10 slow_launch_time 2 socket /var/lib/mysql/mysql.sock sort_buffer_size 2097144 sql_big_selects ON sql_mode sql_notes ON sql_warnings OFF ssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine MyISAM sync_binlog 0 sync_frm ON system_time_zone UTC table_cache 64 table_lock_wait_timeout 50 table_type MyISAM thread_cache_size 0 thread_stack 262144 time_format %H:%i:%s time_zone SYSTEM timed_mutexes OFF tmp_table_size 33554432 tmpdir /tmp/ transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ updatable_views_with_limit YES version 5.0.77 version_bdb Sleepycat Software: Berkeley DB 4.1.24: (January 29, 2009) version_comment Source distribution version_compile_machine x86_64 version_compile_os redhat-linux-gnu wait_timeout 28800
With the index proposed by ypercube, at moment, this is the fastest query (2,107s avg):
SELECT SQL_NO_CACHE int_field, varchar_field FROM big_table INNER JOIN medium_table ON ( ... ) INNER JOIN small_table ON ( ... ) WHERE week BETWEEN #number AND #number AND year BETWEEN #number AND #number AND small_table.pk = 248 AND medium_table.indexed_field = #number
原文：https://stackoverflow.com/questions/6174246
更新时间：2023-08-16 11:08
最满意答案
在不尝试解开大型查询的情况下，可以使用以下日期范围对当前日期敏感：
SalesQty2012=(SELECT Sum(S.SALESQTY)from Sales_Data S where S.PartyCode = a.ACCODE and InvoiceDate BETWEEN '20120101' AND '2012'+right(convert(char(8), getdate(), 112),4)), SalesQty2011=(SELECT Sum(S.SALESQTY)from [GRP022011].[dbo].[Sales_Data] S where S.PartyCode = a.ACCODE and InvoiceDate BETWEEN '20110101' AND '2011'+right(convert(char(8), getdate(), 112),4))使用日期文字的注意事项 - 使用ISO-8601格式YYYYMMDD或YYYY-MM-DDTHH：MM：SS 。 改变的方式是
convert(char(8), getdate(), 112)将当前日期返回为YYYYMMDDRIGHT(.., 4)仅保留MMDD部分'2012' + RIGHT(..)弥补了2012年的MMDD，同样适用于2011年这确保了它总是比较两年之间的相同（相同的日期范围）。
Without trying to unravel your large query, the date ranges can be made to be sensitive to current day using this:
SalesQty2012=(SELECT Sum(S.SALESQTY)from Sales_Data S where S.PartyCode = a.ACCODE and InvoiceDate BETWEEN '20120101' AND '2012'+right(convert(char(8), getdate(), 112),4)), SalesQty2011=(SELECT Sum(S.SALESQTY)from [GRP022011].[dbo].[Sales_Data] S where S.PartyCode = a.ACCODE and InvoiceDate BETWEEN '20110101' AND '2011'+right(convert(char(8), getdate(), 112),4))Note on using date literals - use either ISO-8601 format YYYYMMDD or YYYY-MM-DDTHH:MM:SS. The way the change works is that
convert(char(8), getdate(), 112)returns the current date as YYYYMMDDRIGHT(.., 4)keeps only the MMDD portion'2012' + RIGHT(..)makes up the day MMDD in the year 2012, similarly for 2011This ensures it will always compare like for like (same date range) between the two years.
相关问答
更多-
客户销售趋势查询(Customer Sales Trend Query)[2024-03-05]
在不尝试解开大型查询的情况下，可以使用以下日期范围对当前日期敏感： SalesQty2012=(SELECT Sum(S.SALESQTY)from Sales_Data S where S.PartyCode = a.ACCODE and InvoiceDate BETWEEN '20120101' AND '2012'+right(convert(char(8), getdate(), 112),4)), SalesQty2011=(SELECT ... -
对于icinga来说，在数据库中查询和存储使用idoutils。 配置idodb所需的插件，它们是mysql的模块。 livestatus我认为不会在任何数据库中读取或写入，但更容易获取实时数据并显示所有数据。 For icinga to query and store in the database use idoutils. COnfigure the required plugins for idodb which are modules of mysql. livestatus I think do ...
-
这些领域： customer.PriceLevel = customerRet.PriceLevelRef.FullName.GetValue(); customer.SalesRep = customerRet.SalesRepRef.FullName.GetValue(); 不会永远存在。 您不能假设每个客户PriceLevelRef存在SalesRepRef或PriceLevelRef 。 有些客户没有销售代表，或者没有自定义价格水平。 因此，在尝试获取它们的值之前，请进行测试以确保它们不是NULL ...
-
如果我理解正确，那么您正在根据2011年的数据为客户寻找前50名的销售额 - 并希望查看2011年前50名的所有年份数据，无论这些客户是否在其他年份进入前50名。 试试这个，它可能需要稍微调整，因为我不知道架构，但如果我正确理解这个问题，这应该可以解决问题。 WITH Top50 AS ( SELECT TOP 50 CU.CustomerN ,SUM(ART.SalesAnalysis) AS SalesTotal FROM ...
-
查询每种产品的每日销售和以前的销售情况(query on sales for each day and previous sale for each product also)[2023-07-02]
假设您在表格中包含以下数据： ID NAME SALES_DATE QUANTITY UNIT_PRICE ---- ----- ----------- -------- -------------- 1 p1 2015-07-24 10 5,00 2 p2 2015-07-24 14 10,00 3 p1 2015-07-28 15 4,00 4 p ... -
销售汇总的SQL查询(SQL query for sales summary)[2022-02-21]
SELECT pName, SUM(Price * Quantity) AS SalesValues FROM saleslog WHERE BillDate = '12-10-2010' AND pGroup = 15 GROUP BY pname 我删除Price因为它可能（可能）变化，并不清楚哪一个输出。 如果价格不变，使用这个： SELECT pName, MIN(price), SUM(Price * Quantity) AS SalesValues FROM ... -
如何从服务和销售表中结合前30名客户收入(How to return top 30 customer revenue combines from service and sales table)[2023-10-24]
使用查询包装返回60行的整个查询以获得前30个。 select top 30 x, y, z from ( select top 30 x, y, z from a where blah union all select top 30 x, y, z from b where blah ) x --here's the alias for the subquery order by topcriteria 订单依据很重要，因此您可以获得查询的正确“顶部”。 编辑：这是你的总和示例 ... -
我想我会从这样的事情开始。 对于更大的数据集， data.table通常非常有效。 #Make fake data require("data.table") data <- data.table(customer_ID=c(rep("Josh",3),rep("Ray",4),rep("Eric",3)), sales=c(35,50,65,65,52,49,15,10,13,9)) data[,transaction_num:=seq(1,.N),by=c("cus ...
-
使用AdventureWorks2012查找每位客户的平均销售额(Find Average Sales Amount Per Customer using AdventureWorks2012)[2022-06-28]
您的查询运行 ，它只返回一个空结果集。 如果您查看BusinessEntityContact ，它会将BusinessEntityID （一个客户业务）与PersonID ， PersonID是该业务的联系人。 因此，如果您将查询更改为： SELECT soh.CustomerID AS 'Customer ID', p.FirstName + ' ' + p.LastName AS 'Customer Name', AVG(soh.TotalDue) AS 'Average Sales Amou ... -
我只是使用not exists ： select c.* from customers c where not exists (select 1 from orders o where o.customerid = c.customerid ) order by c.ContactTitle asc; 此外，您不应该使用单引号作为列别名。 它们只应用于字符串和日期常量。 对于不存在的订单，绝对不需要包 ...